﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.OleDb;

namespace AliCMS.CourseModule.OleDb
{
    public partial class Order : OleDbBase, Dal.IOrder
	{
		public int GetInnerIDFromGuid(Guid guid)
		{
			Command.Parameters.Clear();
			Command.Parameters.Add("@" + Alias.Order.ID, MapType(Alias.Order.ID)).Value = guid;
			Command.CommandText = "SELECT " + MapColumn(Alias.Order.InnerID) + " FROM " + MapTable(Alias.Objects.Order) + " WHERE " + MapColumn(Alias.Order.ID) + "=?";
			object result = Command.ExecuteScalar();
			if (result == null)
				return 0;
			else
				return (int)result;
		}
		public Guid GetGuidFromInnerID(object id)
		{
			Command.Parameters.Clear();
			Command.Parameters.Add("@" + Alias.Order.InnerID, MapType(Alias.Order.InnerID)).Value = id;
			Command.CommandText = "SELECT " + MapColumn(Alias.Order.ID) + " FROM " + MapTable(Alias.Objects.Order) + " WHERE " + MapColumn(Alias.Order.InnerID) + "=?";
			object result = Command.ExecuteScalar();
			if (result == null)
				return Guid.Empty;
			else
				return (Guid)result;
		}
		public Dictionary<string, object> Get(object id)
		{
			Dictionary<string, object> result = new Dictionary<string, object>();

			StringBuilder sql = new StringBuilder("SELECT TOP 1");
			sql.Append(" " + MapColumn(Alias.Order.ID));
			sql.Append("," + MapColumn(Alias.Order.InnerID));
			sql.Append("," + MapColumn(Alias.Order.No));
			sql.Append("," + MapColumn(Alias.Order.CourseID));
			sql.Append("," + MapColumn(Alias.Order.CourseName));
			sql.Append("," + MapColumn(Alias.Order.CourseThumbnail));
			sql.Append("," + MapColumn(Alias.Order.TimeUnit));
			sql.Append("," + MapColumn(Alias.Order.TimeAmount));
			sql.Append("," + MapColumn(Alias.Order.UserID));
			sql.Append("," + MapColumn(Alias.Order.UserName));
            sql.Append("," + MapColumn(Alias.Order.UserNickname));
            sql.Append("," + MapColumn(Alias.Order.UserHead));
            sql.Append("," + MapColumn(Alias.Order.TeacherID));
            sql.Append("," + MapColumn(Alias.Order.TeacherName));
            sql.Append("," + MapColumn(Alias.Order.TeacherHead));
            sql.Append("," + MapColumn(Alias.Order.TeacherCommision));
			sql.Append("," + MapColumn(Alias.Order.DistributerID));
			sql.Append("," + MapColumn(Alias.Order.DistributerName));
            sql.Append("," + MapColumn(Alias.Order.DistributerNickname));
            sql.Append("," + MapColumn(Alias.Order.DistributerHead));
            sql.Append("," + MapColumn(Alias.Order.DistributerIsAgent));
			sql.Append("," + MapColumn(Alias.Order.AgentCommision));
			sql.Append("," + MapColumn(Alias.Order.Promotion));
			sql.Append("," + MapColumn(Alias.Order.Price));
			sql.Append("," + MapColumn(Alias.Order.Sum));
			sql.Append("," + MapColumn(Alias.Order.IsPayed));
			sql.Append("," + MapColumn(Alias.Order.PayTime));
			sql.Append("," + MapColumn(Alias.Order.PayMemo));
			sql.Append("," + MapColumn(Alias.Order.PaymentID));
			sql.Append("," + MapColumn(Alias.Order.PaymentName));
			sql.Append("," + MapColumn(Alias.Order.PaymentType));
			sql.Append("," + MapColumn(Alias.Order.PaymentAccountName));
			sql.Append("," + MapColumn(Alias.Order.PaymentAccountNo));
			sql.Append("," + MapColumn(Alias.Order.TradeNo));
			sql.Append("," + MapColumn(Alias.Order.NotifyTime));
			sql.Append("," + MapColumn(Alias.Order.PayOperatorID));
			sql.Append("," + MapColumn(Alias.Order.PayOperatorName));
			sql.Append("," + MapColumn(Alias.Order.RefundTime));
			sql.Append("," + MapColumn(Alias.Order.RefundAmount));
			sql.Append("," + MapColumn(Alias.Order.RefundMemo));
			sql.Append("," + MapColumn(Alias.Order.RefundOperatorID));
			sql.Append("," + MapColumn(Alias.Order.RefundOperatorName));
			sql.Append("," + MapColumn(Alias.Order.IsDelivered));
			sql.Append("," + MapColumn(Alias.Order.DeliverTime));
			sql.Append("," + MapColumn(Alias.Order.DeliverMemo));
			sql.Append("," + MapColumn(Alias.Order.DeliverOperatorID));
			sql.Append("," + MapColumn(Alias.Order.DeliverOperatorName));
			sql.Append("," + MapColumn(Alias.Order.ReturnTime));
			sql.Append("," + MapColumn(Alias.Order.ReturnMemo));
			sql.Append("," + MapColumn(Alias.Order.ReturnOperatorID));
			sql.Append("," + MapColumn(Alias.Order.ReturnOperatorName));
			sql.Append("," + MapColumn(Alias.Order.ExpiredTime));
			sql.Append("," + MapColumn(Alias.Order.IsFinished));
			sql.Append("," + MapColumn(Alias.Order.IsArchived));
			sql.Append("," + MapColumn(Alias.Order.IsCanceled));
			sql.Append("," + MapColumn(Alias.Order.CreatorID));
			sql.Append("," + MapColumn(Alias.Order.CreatorName));
			sql.Append("," + MapColumn(Alias.Order.CreateTime));

			sql.Append(" FROM " + MapTable(Alias.Objects.Order));

			if (id is Guid)
			{
				sql.Append(" WHERE " + MapColumn(Alias.Order.ID) + "=?");

				Command.Parameters.Clear();
				Command.Parameters.Add("@" + Alias.Order.ID, MapType(Alias.Order.ID)).Value = id;
			}
			else
			{
				sql.Append(" WHERE " + MapColumn(Alias.Order.InnerID) + "=?");

				Command.Parameters.Clear();
				Command.Parameters.Add("@" + Alias.Order.InnerID, MapType(Alias.Order.InnerID)).Value = id;
			}

			#region 权限
			if (CheckRight)
			{
				Dictionary<string, string> user = Manager.User;
				if (user != null)
				{
					#region 管理员
					if (user["Namespace"] == "AliCMS.SystemModule")
					{
						if (Convert.ToBoolean(user["HasSelfLimitation"]))
						{
						}
					}
					#endregion
					#region 用户
					else
					{
					}
					#endregion

				}
			}
			#endregion

			Command.CommandText = sql.ToString();
			DataReader = Command.ExecuteReader();
			if (!DataReader.HasRows)
			{
				DataReader.Close();
				return null;
			}

			DataReader.Read();
			int i = 0;
			result[Alias.Order.ID] = DataReader[i++];
			result[Alias.Order.InnerID] = DataReader[i++];
			result[Alias.Order.No] = DataReader[i++];
			result[Alias.Order.CourseID] = DataReader[i++];
			result[Alias.Order.CourseName] = DataReader[i++];
			result[Alias.Order.CourseThumbnail] = DataReader[i++];
			result[Alias.Order.TimeUnit] = DataReader[i++];
			result[Alias.Order.TimeAmount] = DataReader[i++];
			result[Alias.Order.UserID] = DataReader[i++];
			result[Alias.Order.UserName] = DataReader[i++];
            result[Alias.Order.UserNickname] = DataReader[i++];
            result[Alias.Order.UserHead] = DataReader[i++];
            result[Alias.Order.TeacherID] = DataReader[i++];
            result[Alias.Order.TeacherName] = DataReader[i++];
            result[Alias.Order.TeacherHead] = DataReader[i++];
            result[Alias.Order.TeacherCommision] = DataReader[i++];
			result[Alias.Order.DistributerID] = DataReader[i++];
			result[Alias.Order.DistributerName] = DataReader[i++];
            result[Alias.Order.DistributerNickname] = DataReader[i++];
            result[Alias.Order.DistributerHead] = DataReader[i++];
            result[Alias.Order.DistributerIsAgent] = DataReader[i++];
			result[Alias.Order.AgentCommision] = DataReader[i++];
			result[Alias.Order.Promotion] = DataReader[i++];
			result[Alias.Order.Price] = DataReader[i++];
			result[Alias.Order.Sum] = DataReader[i++];
			result[Alias.Order.IsPayed] = DataReader[i++];
			result[Alias.Order.PayTime] = DataReader[i++];
			result[Alias.Order.PayMemo] = DataReader[i++];
			result[Alias.Order.PaymentID] = DataReader[i++];
			result[Alias.Order.PaymentName] = DataReader[i++];
			result[Alias.Order.PaymentType] = DataReader[i++];
			result[Alias.Order.PaymentAccountName] = DataReader[i++];
			result[Alias.Order.PaymentAccountNo] = DataReader[i++];
			result[Alias.Order.TradeNo] = DataReader[i++];
			result[Alias.Order.NotifyTime] = DataReader[i++];
			result[Alias.Order.PayOperatorID] = DataReader[i++];
			result[Alias.Order.PayOperatorName] = DataReader[i++];
			result[Alias.Order.RefundTime] = DataReader[i++];
			result[Alias.Order.RefundAmount] = DataReader[i++];
			result[Alias.Order.RefundMemo] = DataReader[i++];
			result[Alias.Order.RefundOperatorID] = DataReader[i++];
			result[Alias.Order.RefundOperatorName] = DataReader[i++];
			result[Alias.Order.IsDelivered] = DataReader[i++];
			result[Alias.Order.DeliverTime] = DataReader[i++];
			result[Alias.Order.DeliverMemo] = DataReader[i++];
			result[Alias.Order.DeliverOperatorID] = DataReader[i++];
			result[Alias.Order.DeliverOperatorName] = DataReader[i++];
			result[Alias.Order.ReturnTime] = DataReader[i++];
			result[Alias.Order.ReturnMemo] = DataReader[i++];
			result[Alias.Order.ReturnOperatorID] = DataReader[i++];
			result[Alias.Order.ReturnOperatorName] = DataReader[i++];
			result[Alias.Order.ExpiredTime] = DataReader[i++];
			result[Alias.Order.IsFinished] = DataReader[i++];
			result[Alias.Order.IsArchived] = DataReader[i++];
			result[Alias.Order.IsCanceled] = DataReader[i++];
			result[Alias.Order.CreatorID] = DataReader[i++];
			result[Alias.Order.CreatorName] = DataReader[i++];
			result[Alias.Order.CreateTime] = DataReader[i++];

			DataReader.Close();

			return result;
		}
		public void Add(Dictionary<string, object> item)
		{
			Command.Parameters.Clear();

			#region 设置RowLang
			item.Add(Alias.Order.RowLang, "|" + Lang + "|");
			#endregion

			StringBuilder names = new StringBuilder();
			StringBuilder values = new StringBuilder();

			PrepareParameters(names, values, item);

			Command.CommandText = "INSERT INTO " + MapTable(Alias.Objects.Order) + "(" + names + ") VALUES(" + values + ")";
			Command.ExecuteNonQuery();

		}
		public void Modify(int id, Dictionary<string, object> changes)
		{
			Command.Parameters.Clear();
			Command.Parameters.Add("@" + Alias.Order.InnerID, MapType(Alias.Order.InnerID)).Value = id;

			#region 检查并设置RowLang
			Command.CommandText = "SELECT " + MapColumn(Alias.Order.RowLang) + " FROM " + MapTable(Alias.Objects.Order) + " WHERE " + MapColumn(Alias.Order.InnerID) + "=?";
			string rowLang = Command.ExecuteScalar().ToString();
			if (!rowLang.Contains("|" + Lang + "|"))
				changes.Add(Alias.Order.RowLang, rowLang + Lang + "|");
			#endregion

			StringBuilder sql = new StringBuilder("UPDATE " + MapTable(Alias.Objects.Order) + " SET ");
			Command.Parameters.Clear();
			PrepareParameters(sql, changes);
			Command.Parameters.Add("@" + Alias.Order.InnerID, MapType(Alias.Order.InnerID)).Value = id;

			sql.Append(" WHERE " + MapColumn(Alias.Order.InnerID) + "=?");

			#region 权限
			if (CheckRight)
			{
				Dictionary<string, string> user = Manager.User;
				if (user != null)
				{
					#region 管理员
					if (user["Namespace"] == "AliCMS.SystemModule")
					{
						if (Convert.ToBoolean(user["HasSelfLimitation"]))
						{
						}
					}
					#endregion
					#region 用户
					else
					{
					}
					#endregion

				}
			}
			#endregion

			Command.CommandText = sql.ToString();
			Command.ExecuteNonQuery();
		}
		public void Delete(int id)
		{
			Command.Parameters.Clear();
			Command.Parameters.Add("@" + Alias.Order.InnerID, MapType(Alias.Order.InnerID)).Value = id;

			StringBuilder sql = new StringBuilder("DELETE FROM " + MapTable(Alias.Objects.Order) + " WHERE " + MapColumn(Alias.Order.InnerID) + "=?");

			#region 权限
			if (CheckRight)
			{
				Dictionary<string, string> user = Manager.User;
				if (user != null)
				{
					#region 管理员
					if (user["Namespace"] == "AliCMS.SystemModule")
					{
						if (Convert.ToBoolean(user["HasSelfLimitation"]))
						{
						}
					}
					#endregion
					#region 用户
					else
					{
					}
					#endregion

				}
			}
			#endregion

			Command.CommandText = sql.ToString();
			Command.ExecuteNonQuery();
		}
		public DataTable List(int page, int pageSize, string condition, string sort)
		{
			DataTable result = new DataTable();
			result.Columns.Add(Alias.Order.ID, MapCSType(Alias.Order.ID));
			result.Columns.Add(Alias.Order.InnerID, MapCSType(Alias.Order.InnerID));
			result.Columns.Add(Alias.Order.No, MapCSType(Alias.Order.No));
			result.Columns.Add(Alias.Order.CourseID, MapCSType(Alias.Order.CourseID));
			result.Columns.Add(Alias.Order.CourseName, MapCSType(Alias.Order.CourseName));
			result.Columns.Add(Alias.Order.CourseThumbnail, MapCSType(Alias.Order.CourseThumbnail));
			result.Columns.Add(Alias.Order.TimeUnit, MapCSType(Alias.Order.TimeUnit));
			result.Columns.Add(Alias.Order.TimeAmount, MapCSType(Alias.Order.TimeAmount));
			result.Columns.Add(Alias.Order.UserID, MapCSType(Alias.Order.UserID));
			result.Columns.Add(Alias.Order.UserName, MapCSType(Alias.Order.UserName));
            result.Columns.Add(Alias.Order.UserNickname, MapCSType(Alias.Order.UserNickname));
            result.Columns.Add(Alias.Order.UserHead, MapCSType(Alias.Order.UserHead));
            result.Columns.Add(Alias.Order.TeacherID, MapCSType(Alias.Order.TeacherID));
            result.Columns.Add(Alias.Order.TeacherName, MapCSType(Alias.Order.TeacherName));
            result.Columns.Add(Alias.Order.TeacherHead, MapCSType(Alias.Order.TeacherHead));
            result.Columns.Add(Alias.Order.TeacherCommision, MapCSType(Alias.Order.TeacherCommision));
			result.Columns.Add(Alias.Order.DistributerID, MapCSType(Alias.Order.DistributerID));
			result.Columns.Add(Alias.Order.DistributerName, MapCSType(Alias.Order.DistributerName));
            result.Columns.Add(Alias.Order.DistributerNickname, MapCSType(Alias.Order.DistributerNickname));
            result.Columns.Add(Alias.Order.DistributerHead, MapCSType(Alias.Order.DistributerHead));
            result.Columns.Add(Alias.Order.DistributerIsAgent, MapCSType(Alias.Order.DistributerIsAgent));
			result.Columns.Add(Alias.Order.AgentCommision, MapCSType(Alias.Order.AgentCommision));
			result.Columns.Add(Alias.Order.Promotion, MapCSType(Alias.Order.Promotion));
			result.Columns.Add(Alias.Order.Price, MapCSType(Alias.Order.Price));
			result.Columns.Add(Alias.Order.Sum, MapCSType(Alias.Order.Sum));
			result.Columns.Add(Alias.Order.IsPayed, MapCSType(Alias.Order.IsPayed));
			result.Columns.Add(Alias.Order.PayTime, MapCSType(Alias.Order.PayTime));
			result.Columns.Add(Alias.Order.PayMemo, MapCSType(Alias.Order.PayMemo));
			result.Columns.Add(Alias.Order.PaymentID, MapCSType(Alias.Order.PaymentID));
			result.Columns.Add(Alias.Order.PaymentName, MapCSType(Alias.Order.PaymentName));
			result.Columns.Add(Alias.Order.PaymentType, MapCSType(Alias.Order.PaymentType));
			result.Columns.Add(Alias.Order.PaymentAccountName, MapCSType(Alias.Order.PaymentAccountName));
			result.Columns.Add(Alias.Order.PaymentAccountNo, MapCSType(Alias.Order.PaymentAccountNo));
			result.Columns.Add(Alias.Order.TradeNo, MapCSType(Alias.Order.TradeNo));
			result.Columns.Add(Alias.Order.NotifyTime, MapCSType(Alias.Order.NotifyTime));
			result.Columns.Add(Alias.Order.PayOperatorID, MapCSType(Alias.Order.PayOperatorID));
			result.Columns.Add(Alias.Order.PayOperatorName, MapCSType(Alias.Order.PayOperatorName));
			result.Columns.Add(Alias.Order.RefundTime, MapCSType(Alias.Order.RefundTime));
			result.Columns.Add(Alias.Order.RefundAmount, MapCSType(Alias.Order.RefundAmount));
			result.Columns.Add(Alias.Order.RefundMemo, MapCSType(Alias.Order.RefundMemo));
			result.Columns.Add(Alias.Order.RefundOperatorID, MapCSType(Alias.Order.RefundOperatorID));
			result.Columns.Add(Alias.Order.RefundOperatorName, MapCSType(Alias.Order.RefundOperatorName));
			result.Columns.Add(Alias.Order.IsDelivered, MapCSType(Alias.Order.IsDelivered));
			result.Columns.Add(Alias.Order.DeliverTime, MapCSType(Alias.Order.DeliverTime));
			result.Columns.Add(Alias.Order.DeliverMemo, MapCSType(Alias.Order.DeliverMemo));
			result.Columns.Add(Alias.Order.DeliverOperatorID, MapCSType(Alias.Order.DeliverOperatorID));
			result.Columns.Add(Alias.Order.DeliverOperatorName, MapCSType(Alias.Order.DeliverOperatorName));
			result.Columns.Add(Alias.Order.ReturnTime, MapCSType(Alias.Order.ReturnTime));
			result.Columns.Add(Alias.Order.ReturnMemo, MapCSType(Alias.Order.ReturnMemo));
			result.Columns.Add(Alias.Order.ReturnOperatorID, MapCSType(Alias.Order.ReturnOperatorID));
			result.Columns.Add(Alias.Order.ReturnOperatorName, MapCSType(Alias.Order.ReturnOperatorName));
			result.Columns.Add(Alias.Order.ExpiredTime, MapCSType(Alias.Order.ExpiredTime));
			result.Columns.Add(Alias.Order.IsFinished, MapCSType(Alias.Order.IsFinished));
			result.Columns.Add(Alias.Order.IsArchived, MapCSType(Alias.Order.IsArchived));
			result.Columns.Add(Alias.Order.IsCanceled, MapCSType(Alias.Order.IsCanceled));
			result.Columns.Add(Alias.Order.CreatorID, MapCSType(Alias.Order.CreatorID));
			result.Columns.Add(Alias.Order.CreatorName, MapCSType(Alias.Order.CreatorName));
			result.Columns.Add(Alias.Order.CreateTime, MapCSType(Alias.Order.CreateTime));

			#region 条件
			Command.Parameters.Clear();
			StringBuilder where = new StringBuilder();
			if (!String.IsNullOrEmpty(condition))
			{
				where.Append(" WHERE " + Alias.Order.RowLang + " LIKE '%|" + Lang + "|%' AND ");
				where.Append("(" + condition + ")");
				ParseCondition(where, "");
			}
			else
			{
				where.Append(" WHERE " + MapColumn(Alias.Order.RowLang) + " LIKE '%|" + Lang + "|%'");
			}

			#region 权限
			if (CheckRight)
			{
				Dictionary<string, string> user = Manager.User;
				if (user != null)
				{
					#region 管理员
					if (user["Namespace"] == "AliCMS.SystemModule")
					{
						if (Convert.ToBoolean(user["HasSelfLimitation"]))
						{
							if (where.Length > 0)
								where.Append(" AND ");

							where.Append("g." + MapColumn(Alias.Order.CreatorID) + "=?");
							Command.Parameters.Add("@" + Alias.Order.CreatorID, MapType(Alias.Order.CreatorID)).Value = new Guid(user["ID"]);
						}
					}
					#endregion
					#region 用户
					else
					{
					}
					#endregion

				}
			}
			#endregion
			#endregion

			#region 排序
			if (!String.IsNullOrEmpty(sort))
				sort = " ORDER BY " + ParseSort(sort, "");
			else
				sort = " ORDER BY " + MapColumn(Alias.Order.InnerID) + " DESC";
			#endregion

			#region 构造SQL语句
			StringBuilder sql = new StringBuilder("SELECT");
			if (page > 0 && pageSize > 0)
				sql.Append(" TOP " + (page * pageSize));
			sql.Append(" " + MapColumn(Alias.Order.ID));
			sql.Append("," + MapColumn(Alias.Order.InnerID));
			sql.Append("," + MapColumn(Alias.Order.No));
			sql.Append("," + MapColumn(Alias.Order.CourseID));
			sql.Append("," + MapColumn(Alias.Order.CourseName));
			sql.Append("," + MapColumn(Alias.Order.CourseThumbnail));
			sql.Append("," + MapColumn(Alias.Order.TimeUnit));
			sql.Append("," + MapColumn(Alias.Order.TimeAmount));
			sql.Append("," + MapColumn(Alias.Order.UserID));
			sql.Append("," + MapColumn(Alias.Order.UserName));
            sql.Append("," + MapColumn(Alias.Order.DistributerNickname));
            sql.Append("," + MapColumn(Alias.Order.DistributerHead));
            sql.Append("," + MapColumn(Alias.Order.TeacherID));
            sql.Append("," + MapColumn(Alias.Order.TeacherName));
            sql.Append("," + MapColumn(Alias.Order.TeacherHead));
            sql.Append("," + MapColumn(Alias.Order.TeacherCommision));
			sql.Append("," + MapColumn(Alias.Order.DistributerID));
			sql.Append("," + MapColumn(Alias.Order.DistributerName));
            sql.Append("," + MapColumn(Alias.Order.DistributerNickname));
            sql.Append("," + MapColumn(Alias.Order.DistributerHead));
            sql.Append("," + MapColumn(Alias.Order.DistributerIsAgent));
			sql.Append("," + MapColumn(Alias.Order.AgentCommision));
			sql.Append("," + MapColumn(Alias.Order.Promotion));
			sql.Append("," + MapColumn(Alias.Order.Price));
			sql.Append("," + MapColumn(Alias.Order.Sum));
			sql.Append("," + MapColumn(Alias.Order.IsPayed));
			sql.Append("," + MapColumn(Alias.Order.PayTime));
			sql.Append("," + MapColumn(Alias.Order.PayMemo));
			sql.Append("," + MapColumn(Alias.Order.PaymentID));
			sql.Append("," + MapColumn(Alias.Order.PaymentName));
			sql.Append("," + MapColumn(Alias.Order.PaymentType));
			sql.Append("," + MapColumn(Alias.Order.PaymentAccountName));
			sql.Append("," + MapColumn(Alias.Order.PaymentAccountNo));
			sql.Append("," + MapColumn(Alias.Order.TradeNo));
			sql.Append("," + MapColumn(Alias.Order.NotifyTime));
			sql.Append("," + MapColumn(Alias.Order.PayOperatorID));
			sql.Append("," + MapColumn(Alias.Order.PayOperatorName));
			sql.Append("," + MapColumn(Alias.Order.RefundTime));
			sql.Append("," + MapColumn(Alias.Order.RefundAmount));
			sql.Append("," + MapColumn(Alias.Order.RefundMemo));
			sql.Append("," + MapColumn(Alias.Order.RefundOperatorID));
			sql.Append("," + MapColumn(Alias.Order.RefundOperatorName));
			sql.Append("," + MapColumn(Alias.Order.IsDelivered));
			sql.Append("," + MapColumn(Alias.Order.DeliverTime));
			sql.Append("," + MapColumn(Alias.Order.DeliverMemo));
			sql.Append("," + MapColumn(Alias.Order.DeliverOperatorID));
			sql.Append("," + MapColumn(Alias.Order.DeliverOperatorName));
			sql.Append("," + MapColumn(Alias.Order.ReturnTime));
			sql.Append("," + MapColumn(Alias.Order.ReturnMemo));
			sql.Append("," + MapColumn(Alias.Order.ReturnOperatorID));
			sql.Append("," + MapColumn(Alias.Order.ReturnOperatorName));
			sql.Append("," + MapColumn(Alias.Order.ExpiredTime));
			sql.Append("," + MapColumn(Alias.Order.IsFinished));
			sql.Append("," + MapColumn(Alias.Order.IsArchived));
			sql.Append("," + MapColumn(Alias.Order.IsCanceled));
			sql.Append("," + MapColumn(Alias.Order.CreatorID));
			sql.Append("," + MapColumn(Alias.Order.CreatorName));
			sql.Append("," + MapColumn(Alias.Order.CreateTime));
			sql.Append(" FROM " + MapTable(Alias.Objects.Order));
			sql.Append(where + sort);
			Command.CommandText = sql.ToString();
			#endregion

			#region 执行SQL语句并获取记录
			int curIndex = 0, rowIndex = 0;
			int startIndex = (page - 1) * pageSize;
			DataReader = Command.ExecuteReader();
			while (DataReader.Read())
			{
				if (pageSize != -1)
				{
					if (curIndex < startIndex)
					{
						curIndex++;
						continue;
					}

					if (rowIndex == pageSize)
						break;
				}

				DataRow row = result.NewRow();
				int i = 0;
				row[Alias.Order.ID] = DataReader[i++];
				row[Alias.Order.InnerID] = DataReader[i++];
				row[Alias.Order.No] = DataReader[i++];
				row[Alias.Order.CourseID] = DataReader[i++];
				row[Alias.Order.CourseName] = DataReader[i++];
				row[Alias.Order.CourseThumbnail] = DataReader[i++];
				row[Alias.Order.TimeUnit] = DataReader[i++];
				row[Alias.Order.TimeAmount] = DataReader[i++];
				row[Alias.Order.UserID] = DataReader[i++];
				row[Alias.Order.UserName] = DataReader[i++];
                row[Alias.Order.UserNickname] = DataReader[i++];
                row[Alias.Order.UserHead] = DataReader[i++];
                row[Alias.Order.TeacherID] = DataReader[i++];
                row[Alias.Order.TeacherName] = DataReader[i++];
                row[Alias.Order.TeacherHead] = DataReader[i++];
                row[Alias.Order.TeacherCommision] = DataReader[i++];
				row[Alias.Order.DistributerID] = DataReader[i++];
				row[Alias.Order.DistributerName] = DataReader[i++];
                row[Alias.Order.DistributerNickname] = DataReader[i++];
                row[Alias.Order.DistributerHead] = DataReader[i++];
                row[Alias.Order.DistributerIsAgent] = DataReader[i++];
				row[Alias.Order.AgentCommision] = DataReader[i++];
				row[Alias.Order.Promotion] = DataReader[i++];
				row[Alias.Order.Price] = DataReader[i++];
				row[Alias.Order.Sum] = DataReader[i++];
				row[Alias.Order.IsPayed] = DataReader[i++];
				row[Alias.Order.PayTime] = DataReader[i++];
				row[Alias.Order.PayMemo] = DataReader[i++];
				row[Alias.Order.PaymentID] = DataReader[i++];
				row[Alias.Order.PaymentName] = DataReader[i++];
				row[Alias.Order.PaymentType] = DataReader[i++];
				row[Alias.Order.PaymentAccountName] = DataReader[i++];
				row[Alias.Order.PaymentAccountNo] = DataReader[i++];
				row[Alias.Order.TradeNo] = DataReader[i++];
				row[Alias.Order.NotifyTime] = DataReader[i++];
				row[Alias.Order.PayOperatorID] = DataReader[i++];
				row[Alias.Order.PayOperatorName] = DataReader[i++];
				row[Alias.Order.RefundTime] = DataReader[i++];
				row[Alias.Order.RefundAmount] = DataReader[i++];
				row[Alias.Order.RefundMemo] = DataReader[i++];
				row[Alias.Order.RefundOperatorID] = DataReader[i++];
				row[Alias.Order.RefundOperatorName] = DataReader[i++];
				row[Alias.Order.IsDelivered] = DataReader[i++];
				row[Alias.Order.DeliverTime] = DataReader[i++];
				row[Alias.Order.DeliverMemo] = DataReader[i++];
				row[Alias.Order.DeliverOperatorID] = DataReader[i++];
				row[Alias.Order.DeliverOperatorName] = DataReader[i++];
				row[Alias.Order.ReturnTime] = DataReader[i++];
				row[Alias.Order.ReturnMemo] = DataReader[i++];
				row[Alias.Order.ReturnOperatorID] = DataReader[i++];
				row[Alias.Order.ReturnOperatorName] = DataReader[i++];
				row[Alias.Order.ExpiredTime] = DataReader[i++];
				row[Alias.Order.IsFinished] = DataReader[i++];
				row[Alias.Order.IsArchived] = DataReader[i++];
				row[Alias.Order.IsCanceled] = DataReader[i++];
				row[Alias.Order.CreatorID] = DataReader[i++];
				row[Alias.Order.CreatorName] = DataReader[i++];
				row[Alias.Order.CreateTime] = DataReader[i++];

				result.Rows.Add(row);

				rowIndex++;
			}
			DataReader.Close();
			#endregion

			return result;
		}
		public int GetRecordCount(string condition)
		{
			#region 条件
			Command.Parameters.Clear();
			StringBuilder where = new StringBuilder();
			if (!String.IsNullOrEmpty(condition))
			{
				where.Append(" WHERE " + Alias.Order.RowLang + " LIKE '%|" + Lang + "|%' AND ");
				where.Append("(" + condition + ")");
				ParseCondition(where, "");
			}
			else
			{
				where.Append(" WHERE " + MapColumn(Alias.Order.RowLang) + " LIKE '%|" + Lang + "|%'");
			}

			#region 权限
			if (CheckRight)
			{
				Dictionary<string, string> user = Manager.User;
				if (user != null)
				{
					#region 管理员
					if (user["Namespace"] == "AliCMS.SystemModule")
					{
						if (Convert.ToBoolean(user["HasSelfLimitation"]))
						{
							if (where.Length > 0)
								where.Append(" AND ");

							where.Append("g." + MapColumn(Alias.Order.CreatorID) + "=?");
							Command.Parameters.Add("@" + Alias.Order.CreatorID, MapType(Alias.Order.CreatorID)).Value = new Guid(user["ID"]);
						}
					}
					#endregion
					#region 用户
					else
					{
					}
					#endregion

				}
			}
			#endregion
			#endregion

			Command.CommandText = "SELECT COUNT(*) FROM " + MapTable(Alias.Objects.Order) + where;
			return Convert.ToInt32(Command.ExecuteScalar());
		}

        #region 统计
        public string Stat(Dictionary<string, object> data)
        {
            Command.Parameters.Clear();
            if (data.ContainsKey("IsFinished") && !String.IsNullOrEmpty(data["IsFinished"].ToString()))
                Command.Parameters.Add("@IsFinished", MapType(Alias.Order.IsFinished)).Value = data["IsFinished"];
            if (data.ContainsKey("IsArchived") && !String.IsNullOrEmpty(data["IsArchived"].ToString()))
                Command.Parameters.Add("@IsArchived", MapType(Alias.Order.IsArchived)).Value = data["IsArchived"];
            if (data.ContainsKey("IsCanceled") && !String.IsNullOrEmpty(data["IsCanceled"].ToString()))
                Command.Parameters.Add("@IsCanceled", MapType(Alias.Order.IsCanceled)).Value = data["IsCanceled"];
            if (data.ContainsKey("IsPayed") && !String.IsNullOrEmpty(data["IsPayed"].ToString()))
                Command.Parameters.Add("@IsPayed", MapType(Alias.Order.IsPayed)).Value = data["IsPayed"];
            if (data.ContainsKey("IsDelivered") && !String.IsNullOrEmpty(data["IsDelivered"].ToString()))
                Command.Parameters.Add("@IsDelivered", MapType(Alias.Order.IsDelivered)).Value = data["IsDelivered"];
            if (data.ContainsKey("Start") && !String.IsNullOrEmpty(data["Start"].ToString()))
                Command.Parameters.Add("@Start", MapType(Alias.Order.CreateTime)).Value = data["Start"];
            if (data.ContainsKey("End") && !String.IsNullOrEmpty(data["End"].ToString()))
                Command.Parameters.Add("@End", MapType(Alias.Order.CreateTime)).Value = data["End"];

            string sql = "SELECT COUNT(*) AS [Count], SUM(" + MapColumn(Alias.Order.Sum) + ") as [Sum], SUM(" + MapColumn(Alias.Order.AgentCommision) + ") as [AgentCommision], SUM(" + MapColumn(Alias.Order.TeacherCommision) + ") as [TeacherCommision] FROM " + MapTable(Alias.Objects.Order);
            string and = null;
            if (data.ContainsKey("IsFinished") && !String.IsNullOrEmpty(data["IsFinished"].ToString()))
            {
                if (and == null)
                    and = " WHERE ";
                else
                    and = " and ";
                sql += and + MapColumn(Alias.Order.IsFinished) + "=?";
            }
            if (data.ContainsKey("IsArchived") && !String.IsNullOrEmpty(data["IsArchived"].ToString()))
            {
                if (and == null)
                    and = " WHERE ";
                else
                    and = " and ";
                sql += and + MapColumn(Alias.Order.IsArchived) + "=?";
            }
            if (data.ContainsKey("IsCanceled") && !String.IsNullOrEmpty(data["IsCanceled"].ToString()))
            {
                if (and == null)
                    and = " WHERE ";
                else
                    and = " and ";
                sql += and + MapColumn(Alias.Order.IsCanceled) + "=?";
            }
            if (data.ContainsKey("IsPayed") && !String.IsNullOrEmpty(data["IsPayed"].ToString()))
            {
                if (and == null)
                    and = " WHERE ";
                else
                    and = " and ";
                sql += and + MapColumn(Alias.Order.IsPayed) + "=?";
            }
            if (data.ContainsKey("IsDelivered") && !String.IsNullOrEmpty(data["IsDelivered"].ToString()))
            {
                if (and == null)
                    and = " WHERE ";
                else
                    and = " and ";
                sql += and + MapColumn(Alias.Order.IsDelivered) + "=?";
            }
            if (data.ContainsKey("Start") && !String.IsNullOrEmpty(data["Start"].ToString()))
            {
                if (and == null)
                    and = " WHERE ";
                else
                    and = " and ";
                sql += and + MapColumn(Alias.Order.CreateTime) + ">=?";
            }
            if (data.ContainsKey("End") && !String.IsNullOrEmpty(data["End"].ToString()))
            {
                if (and == null)
                    and = " WHERE ";
                else
                    and = " and ";
                sql += and + MapColumn(Alias.Order.CreateTime) + "<=?";
            }

            Command.CommandText = sql;

            string result;
            DataReader = Command.ExecuteReader();
            if (DataReader.Read())
            {
                result = "{\"Count\":" + DataReader["Count"] + ",\"Sum\":" + DataReader["Sum"] + ",\"AgentCommision\":" + DataReader["AgentCommision"] + ",\"TeacherCommision\":" + DataReader["TeacherCommision"] + "}";
            }
            else
            {
                result = "{\"Count\":0,\"Sum\":0,\"AgentCommision\":0,\"ParentAgentCommision\":0,\"GrandAgentCommision\":0,\"TeacherCommision\":0}";
            }
            DataReader.Close();

            return result;
        }
        public string StatHourly(Dictionary<string, object> data)
        {
            Command.Parameters.Clear();
            if (data.ContainsKey("IsFinished") && !String.IsNullOrEmpty(data["IsFinished"].ToString()))
                Command.Parameters.Add("@IsFinished", MapType(Alias.Order.IsFinished)).Value = data["IsFinished"];
            if (data.ContainsKey("IsArchived") && !String.IsNullOrEmpty(data["IsArchived"].ToString()))
                Command.Parameters.Add("@IsArchived", MapType(Alias.Order.IsArchived)).Value = data["IsArchived"];
            if (data.ContainsKey("IsCanceled") && !String.IsNullOrEmpty(data["IsCanceled"].ToString()))
                Command.Parameters.Add("@IsCanceled", MapType(Alias.Order.IsCanceled)).Value = data["IsCanceled"];
            if (data.ContainsKey("IsPayed") && !String.IsNullOrEmpty(data["IsPayed"].ToString()))
                Command.Parameters.Add("@IsPayed", MapType(Alias.Order.IsPayed)).Value = data["IsPayed"];
            if (data.ContainsKey("IsDelivered") && !String.IsNullOrEmpty(data["IsDelivered"].ToString()))
                Command.Parameters.Add("@IsDelivered", MapType(Alias.Order.IsDelivered)).Value = data["IsDelivered"];
            if (data.ContainsKey("Start") && !String.IsNullOrEmpty(data["Start"].ToString()))
                Command.Parameters.Add("@Start", MapType(Alias.Order.CreateTime)).Value = data["Start"];
            if (data.ContainsKey("End") && !String.IsNullOrEmpty(data["End"].ToString()))
                Command.Parameters.Add("@End", MapType(Alias.Order.CreateTime)).Value = data["End"];

            string sql = "SELECT cast(substring(CONVERT(varchar," + MapColumn(Alias.Order.CreateTime) + ",120), 12, 2) as int) as [Hour], COUNT(*) as [Count], SUM(" + MapColumn(Alias.Order.Sum) + ") as [Sum], SUM(" + MapColumn(Alias.Order.AgentCommision) + ") as [AgentCommision], SUM(" + MapColumn(Alias.Order.TeacherCommision) + ") as [TeacherCommision] FROM " + MapTable(Alias.Objects.Order);
            string and = null;
            if (data.ContainsKey("IsFinished") && !String.IsNullOrEmpty(data["IsFinished"].ToString()))
            {
                if (and == null)
                    and = " WHERE ";
                else
                    and = " and ";
                sql += and + MapColumn(Alias.Order.IsFinished) + "=?";
            }
            if (data.ContainsKey("IsArchived") && !String.IsNullOrEmpty(data["IsArchived"].ToString()))
            {
                if (and == null)
                    and = " WHERE ";
                else
                    and = " and ";
                sql += and + MapColumn(Alias.Order.IsArchived) + "=?";
            }
            if (data.ContainsKey("IsCanceled") && !String.IsNullOrEmpty(data["IsCanceled"].ToString()))
            {
                if (and == null)
                    and = " WHERE ";
                else
                    and = " and ";
                sql += and + MapColumn(Alias.Order.IsCanceled) + "=?";
            }
            if (data.ContainsKey("IsPayed") && !String.IsNullOrEmpty(data["IsPayed"].ToString()))
            {
                if (and == null)
                    and = " WHERE ";
                else
                    and = " and ";
                sql += and + MapColumn(Alias.Order.IsPayed) + "=?";
            }
            if (data.ContainsKey("IsDelivered") && !String.IsNullOrEmpty(data["IsDelivered"].ToString()))
            {
                if (and == null)
                    and = " WHERE ";
                else
                    and = " and ";
                sql += and + MapColumn(Alias.Order.IsDelivered) + "=?";
            }
            if (data.ContainsKey("Start") && !String.IsNullOrEmpty(data["Start"].ToString()))
            {
                if (and == null)
                    and = " WHERE ";
                else
                    and = " and ";
                sql += and + MapColumn(Alias.Order.CreateTime) + ">=?";
            }
            if (data.ContainsKey("End") && !String.IsNullOrEmpty(data["End"].ToString()))
            {
                if (and == null)
                    and = " WHERE ";
                else
                    and = " and ";
                sql += and + MapColumn(Alias.Order.CreateTime) + "<=?";
            }

            sql += " GROUP BY cast(substring(CONVERT(varchar,CreateTime,120), 12, 2) as int)";
            sql += " ORDER BY [Hour] ASC";

            Command.CommandText = sql;

            OleDbDataAdapter adapter = new OleDbDataAdapter();
            adapter.SelectCommand = Command;
            DataTable result = new DataTable();
            adapter.Fill(result);

            return Manager.BuildJson(result);
        }
        public string StatDaily(Dictionary<string, object> data)
        {
            Command.Parameters.Clear();
            if (data.ContainsKey("IsFinished") && !String.IsNullOrEmpty(data["IsFinished"].ToString()))
                Command.Parameters.Add("@IsFinished", MapType(Alias.Order.IsFinished)).Value = data["IsFinished"];
            if (data.ContainsKey("IsArchived") && !String.IsNullOrEmpty(data["IsArchived"].ToString()))
                Command.Parameters.Add("@IsArchived", MapType(Alias.Order.IsArchived)).Value = data["IsArchived"];
            if (data.ContainsKey("IsCanceled") && !String.IsNullOrEmpty(data["IsCanceled"].ToString()))
                Command.Parameters.Add("@IsCanceled", MapType(Alias.Order.IsCanceled)).Value = data["IsCanceled"];
            if (data.ContainsKey("IsPayed") && !String.IsNullOrEmpty(data["IsPayed"].ToString()))
                Command.Parameters.Add("@IsPayed", MapType(Alias.Order.IsPayed)).Value = data["IsPayed"];
            if (data.ContainsKey("IsDelivered") && !String.IsNullOrEmpty(data["IsDelivered"].ToString()))
                Command.Parameters.Add("@IsDelivered", MapType(Alias.Order.IsDelivered)).Value = data["IsDelivered"];
            if (data.ContainsKey("Start") && !String.IsNullOrEmpty(data["Start"].ToString()))
                Command.Parameters.Add("@Start", MapType(Alias.Order.CreateTime)).Value = data["Start"];
            if (data.ContainsKey("End") && !String.IsNullOrEmpty(data["End"].ToString()))
                Command.Parameters.Add("@End", MapType(Alias.Order.CreateTime)).Value = data["End"];

            string sql = "SELECT CONVERT(varchar(10) , CreateTime, 23) as [Day], COUNT(*) as [Count], SUM(" + MapColumn(Alias.Order.Sum) + ") as [Sum], SUM(" + MapColumn(Alias.Order.AgentCommision) + ") as [AgentCommision], SUM(" + MapColumn(Alias.Order.TeacherCommision) + ") as [TeacherCommision] FROM " + MapTable(Alias.Objects.Order);
            string and = null;
            if (data.ContainsKey("IsFinished") && !String.IsNullOrEmpty(data["IsFinished"].ToString()))
            {
                if (and == null)
                    and = " WHERE ";
                else
                    and = " and ";
                sql += and + MapColumn(Alias.Order.IsFinished) + "=?";
            }
            if (data.ContainsKey("IsArchived") && !String.IsNullOrEmpty(data["IsArchived"].ToString()))
            {
                if (and == null)
                    and = " WHERE ";
                else
                    and = " and ";
                sql += and + MapColumn(Alias.Order.IsArchived) + "=?";
            }
            if (data.ContainsKey("IsCanceled") && !String.IsNullOrEmpty(data["IsCanceled"].ToString()))
            {
                if (and == null)
                    and = " WHERE ";
                else
                    and = " and ";
                sql += and + MapColumn(Alias.Order.IsCanceled) + "=?";
            }
            if (data.ContainsKey("IsPayed") && !String.IsNullOrEmpty(data["IsPayed"].ToString()))
            {
                if (and == null)
                    and = " WHERE ";
                else
                    and = " and ";
                sql += and + MapColumn(Alias.Order.IsPayed) + "=?";
            }
            if (data.ContainsKey("IsDelivered") && !String.IsNullOrEmpty(data["IsDelivered"].ToString()))
            {
                if (and == null)
                    and = " WHERE ";
                else
                    and = " and ";
                sql += and + MapColumn(Alias.Order.IsDelivered) + "=?";
            }
            if (data.ContainsKey("Start") && !String.IsNullOrEmpty(data["Start"].ToString()))
            {
                if (and == null)
                    and = " WHERE ";
                else
                    and = " and ";
                sql += and + MapColumn(Alias.Order.CreateTime) + ">=?";
            }
            if (data.ContainsKey("End") && !String.IsNullOrEmpty(data["End"].ToString()))
            {
                if (and == null)
                    and = " WHERE ";
                else
                    and = " and ";
                sql += and + MapColumn(Alias.Order.CreateTime) + "<=?";
            }

            sql += " GROUP BY CONVERT(varchar(10) , CreateTime, 23)";
            sql += " ORDER BY [Day] ASC";

            Command.CommandText = sql;

            OleDbDataAdapter adapter = new OleDbDataAdapter();
            adapter.SelectCommand = Command;
            DataTable result = new DataTable();
            adapter.Fill(result);

            return Manager.BuildJson(result);
        }
        public string StatMonthly(Dictionary<string, object> data)
        {
            Command.Parameters.Clear();
            if (data.ContainsKey("IsFinished") && !String.IsNullOrEmpty(data["IsFinished"].ToString()))
                Command.Parameters.Add("@IsFinished", MapType(Alias.Order.IsFinished)).Value = data["IsFinished"];
            if (data.ContainsKey("IsArchived") && !String.IsNullOrEmpty(data["IsArchived"].ToString()))
                Command.Parameters.Add("@IsArchived", MapType(Alias.Order.IsArchived)).Value = data["IsArchived"];
            if (data.ContainsKey("IsCanceled") && !String.IsNullOrEmpty(data["IsCanceled"].ToString()))
                Command.Parameters.Add("@IsCanceled", MapType(Alias.Order.IsCanceled)).Value = data["IsCanceled"];
            if (data.ContainsKey("IsPayed") && !String.IsNullOrEmpty(data["IsPayed"].ToString()))
                Command.Parameters.Add("@IsPayed", MapType(Alias.Order.IsPayed)).Value = data["IsPayed"];
            if (data.ContainsKey("IsDelivered") && !String.IsNullOrEmpty(data["IsDelivered"].ToString()))
                Command.Parameters.Add("@IsDelivered", MapType(Alias.Order.IsDelivered)).Value = data["IsDelivered"];
            if (data.ContainsKey("Start") && !String.IsNullOrEmpty(data["Start"].ToString()))
                Command.Parameters.Add("@Start", MapType(Alias.Order.CreateTime)).Value = data["Start"];
            if (data.ContainsKey("End") && !String.IsNullOrEmpty(data["End"].ToString()))
                Command.Parameters.Add("@End", MapType(Alias.Order.CreateTime)).Value = data["End"];

            string sql = "SELECT CONVERT(varchar(7) , CreateTime, 23) as [Month], COUNT(*) as [Count], SUM(" + MapColumn(Alias.Order.Sum) + ") as [Sum], SUM(" + MapColumn(Alias.Order.AgentCommision) + ") as [AgentCommision], SUM(" + MapColumn(Alias.Order.TeacherCommision) + ") as [TeacherCommision] FROM " + MapTable(Alias.Objects.Order);
            string and = null;
            if (data.ContainsKey("IsFinished") && !String.IsNullOrEmpty(data["IsFinished"].ToString()))
            {
                if (and == null)
                    and = " WHERE ";
                else
                    and = " and ";
                sql += and + MapColumn(Alias.Order.IsFinished) + "=?";
            }
            if (data.ContainsKey("IsArchived") && !String.IsNullOrEmpty(data["IsArchived"].ToString()))
            {
                if (and == null)
                    and = " WHERE ";
                else
                    and = " and ";
                sql += and + MapColumn(Alias.Order.IsArchived) + "=?";
            }
            if (data.ContainsKey("IsCanceled") && !String.IsNullOrEmpty(data["IsCanceled"].ToString()))
            {
                if (and == null)
                    and = " WHERE ";
                else
                    and = " and ";
                sql += and + MapColumn(Alias.Order.IsCanceled) + "=?";
            }
            if (data.ContainsKey("IsPayed") && !String.IsNullOrEmpty(data["IsPayed"].ToString()))
            {
                if (and == null)
                    and = " WHERE ";
                else
                    and = " and ";
                sql += and + MapColumn(Alias.Order.IsPayed) + "=?";
            }
            if (data.ContainsKey("IsDelivered") && !String.IsNullOrEmpty(data["IsDelivered"].ToString()))
            {
                if (and == null)
                    and = " WHERE ";
                else
                    and = " and ";
                sql += and + MapColumn(Alias.Order.IsDelivered) + "=?";
            }
            if (data.ContainsKey("Start") && !String.IsNullOrEmpty(data["Start"].ToString()))
            {
                if (and == null)
                    and = " WHERE ";
                else
                    and = " and ";
                sql += and + MapColumn(Alias.Order.CreateTime) + ">=?";
            }
            if (data.ContainsKey("End") && !String.IsNullOrEmpty(data["End"].ToString()))
            {
                if (and == null)
                    and = " WHERE ";
                else
                    and = " and ";
                sql += and + MapColumn(Alias.Order.CreateTime) + "<=?";
            }

            sql += " GROUP BY CONVERT(varchar(7) , CreateTime, 23)";
            sql += " ORDER BY [Month] ASC";

            Command.CommandText = sql;

            OleDbDataAdapter adapter = new OleDbDataAdapter();
            adapter.SelectCommand = Command;
            DataTable result = new DataTable();
            adapter.Fill(result);

            return Manager.BuildJson(result);
        }
        #endregion

        #region 教师统计
        public string Teacher_StatOne(Guid teacherId, Dictionary<string, object> data)
        {
            Command.Parameters.Clear();
            Command.Parameters.Add("@" + Alias.Order.TeacherID, MapType(Alias.Order.TeacherID)).Value = teacherId;
            if (data.ContainsKey("IsFinished") && !String.IsNullOrEmpty(data["IsFinished"].ToString()))
                Command.Parameters.Add("@IsFinished", MapType(Alias.Order.IsFinished)).Value = data["IsFinished"];
            if (data.ContainsKey("IsArchived") && !String.IsNullOrEmpty(data["IsArchived"].ToString()))
                Command.Parameters.Add("@IsArchived", MapType(Alias.Order.IsArchived)).Value = data["IsArchived"];
            if (data.ContainsKey("IsCanceled") && !String.IsNullOrEmpty(data["IsCanceled"].ToString()))
                Command.Parameters.Add("@IsCanceled", MapType(Alias.Order.IsCanceled)).Value = data["IsCanceled"];
            if (data.ContainsKey("IsPayed") && !String.IsNullOrEmpty(data["IsPayed"].ToString()))
                Command.Parameters.Add("@IsPayed", MapType(Alias.Order.IsPayed)).Value = data["IsPayed"];
            if (data.ContainsKey("IsDelivered") && !String.IsNullOrEmpty(data["IsDelivered"].ToString()))
                Command.Parameters.Add("@IsDelivered", MapType(Alias.Order.IsDelivered)).Value = data["IsDelivered"];
            if (data.ContainsKey("Start") && !String.IsNullOrEmpty(data["Start"].ToString()))
                Command.Parameters.Add("@Start", MapType(Alias.Order.CreateTime)).Value = data["Start"];
            if (data.ContainsKey("End") && !String.IsNullOrEmpty(data["End"].ToString()))
                Command.Parameters.Add("@End", MapType(Alias.Order.CreateTime)).Value = data["End"];

            string sql = "SELECT COUNT(*) AS [Count], SUM(" + MapColumn(Alias.Order.Sum) + ") as [Sum], SUM(" + MapColumn(Alias.Order.TeacherCommision) + ") as [TeacherCommision] FROM " + MapTable(Alias.Objects.Order) + " WHERE " + MapColumn(Alias.Order.TeacherID) + "=?";
            if (data.ContainsKey("IsFinished") && !String.IsNullOrEmpty(data["IsFinished"].ToString()))
                sql += " and " + MapColumn(Alias.Order.IsFinished) + "=?";
            if (data.ContainsKey("IsArchived") && !String.IsNullOrEmpty(data["IsArchived"].ToString()))
                sql += " and " + MapColumn(Alias.Order.IsArchived) + "=?";
            if (data.ContainsKey("IsCanceled") && !String.IsNullOrEmpty(data["IsCanceled"].ToString()))
                sql += " and " + MapColumn(Alias.Order.IsCanceled) + "=?";
            if (data.ContainsKey("IsPayed") && !String.IsNullOrEmpty(data["IsPayed"].ToString()))
                sql += " and " + MapColumn(Alias.Order.IsPayed) + "=?";
            if (data.ContainsKey("IsDelivered") && !String.IsNullOrEmpty(data["IsDelivered"].ToString()))
                sql += " and " + MapColumn(Alias.Order.IsDelivered) + "=?";
            if (data.ContainsKey("Start") && !String.IsNullOrEmpty(data["Start"].ToString()))
                sql += " and " + MapColumn(Alias.Order.CreateTime) + ">=?";
            if (data.ContainsKey("End") && !String.IsNullOrEmpty(data["End"].ToString()))
                sql += " and " + MapColumn(Alias.Order.CreateTime) + "<=?";

            Command.CommandText = sql;

            string result;
            DataReader = Command.ExecuteReader();
            if (DataReader.Read())
            {
                result = "{\"Count\":" + DataReader["Count"] + ",\"Sum\":" + DataReader["Sum"] + ",\"TeacherCommision\":" + DataReader["TeacherCommision"] + "}";
            }
            else
            {
                result = "{\"Count\":0,\"Sum\":0,\"TeacherCommision\":0}";
            }
            DataReader.Close();

            return result;
        }
        public string Teacher_StatAll(Dictionary<string, object> data)
        {
            Command.Parameters.Clear();
            if (data.ContainsKey("IsFinished") && !String.IsNullOrEmpty(data["IsFinished"].ToString()))
                Command.Parameters.Add("@IsFinished", MapType(Alias.Order.IsFinished)).Value = data["IsFinished"];
            if (data.ContainsKey("IsArchived") && !String.IsNullOrEmpty(data["IsArchived"].ToString()))
                Command.Parameters.Add("@IsArchived", MapType(Alias.Order.IsArchived)).Value = data["IsArchived"];
            if (data.ContainsKey("IsCanceled") && !String.IsNullOrEmpty(data["IsCanceled"].ToString()))
                Command.Parameters.Add("@IsCanceled", MapType(Alias.Order.IsCanceled)).Value = data["IsCanceled"];
            if (data.ContainsKey("IsPayed") && !String.IsNullOrEmpty(data["IsPayed"].ToString()))
                Command.Parameters.Add("@IsPayed", MapType(Alias.Order.IsPayed)).Value = data["IsPayed"];
            if (data.ContainsKey("IsDelivered") && !String.IsNullOrEmpty(data["IsDelivered"].ToString()))
                Command.Parameters.Add("@IsDelivered", MapType(Alias.Order.IsDelivered)).Value = data["IsDelivered"];
            if (data.ContainsKey("Start") && !String.IsNullOrEmpty(data["Start"].ToString()))
                Command.Parameters.Add("@Start", MapType(Alias.Order.CreateTime)).Value = data["Start"];
            if (data.ContainsKey("End") && !String.IsNullOrEmpty(data["End"].ToString()))
                Command.Parameters.Add("@End", MapType(Alias.Order.CreateTime)).Value = data["End"];

            string sql = "SELECT TeacherID, COUNT(*) as [Count], SUM(" + MapColumn(Alias.Order.Sum) + ") as [Sum], SUM(" + MapColumn(Alias.Order.TeacherCommision) + ") as [TeacherCommision] FROM " + MapTable(Alias.Objects.Order) + " WHERE " + MapColumn(Alias.Order.TeacherID) + " IS NOT NULL";
            if (data.ContainsKey("IsFinished") && !String.IsNullOrEmpty(data["IsFinished"].ToString()))
                sql += " and " + MapColumn(Alias.Order.IsFinished) + "=?";
            if (data.ContainsKey("IsArchived") && !String.IsNullOrEmpty(data["IsArchived"].ToString()))
                sql += " and " + MapColumn(Alias.Order.IsArchived) + "=?";
            if (data.ContainsKey("IsCanceled") && !String.IsNullOrEmpty(data["IsCanceled"].ToString()))
                sql += " and " + MapColumn(Alias.Order.IsCanceled) + "=?";
            if (data.ContainsKey("IsPayed") && !String.IsNullOrEmpty(data["IsPayed"].ToString()))
                sql += " and " + MapColumn(Alias.Order.IsPayed) + "=?";
            if (data.ContainsKey("IsDelivered") && !String.IsNullOrEmpty(data["IsDelivered"].ToString()))
                sql += " and " + MapColumn(Alias.Order.IsDelivered) + "=?";
            if (data.ContainsKey("Start") && !String.IsNullOrEmpty(data["Start"].ToString()))
                sql += " and " + MapColumn(Alias.Order.CreateTime) + ">=?";
            if (data.ContainsKey("End") && !String.IsNullOrEmpty(data["End"].ToString()))
                sql += " and " + MapColumn(Alias.Order.CreateTime) + "<=?";

            sql += " GROUP BY TeacherID";
            sql += " ORDER BY [Count] DESC";

            Command.CommandText = sql;

            OleDbDataAdapter adapter = new OleDbDataAdapter();
            adapter.SelectCommand = Command;
            DataTable result = new DataTable();
            adapter.Fill(result);

            #region 增加教师名称

            #region 教师列表
            Command.Parameters.Clear();
            Command.CommandText = "SELECT " + MapColumn(Alias.Teacher.ID) + "," + MapColumn(Alias.Teacher.Name, Lang) + "," + MapColumn(Alias.Teacher.InnerID) + " FROM " + MapTable(Alias.Objects.Teacher);
            DataTable teacherList = new DataTable();
            adapter.Fill(teacherList);
            #endregion

            result.Columns.Add("TeacherName", MapCSType(Alias.Teacher.Name));
            result.Columns.Add("TeacherInnerID", MapCSType(Alias.Teacher.InnerID));
            foreach (DataRow row in result.Rows)
            {
                DataRow[] rows = teacherList.Select("ID='" + row["TeacherID"] + "'");
                if (rows.Length > 0)
                {
                    row["TeacherName"] = rows[0][1];
                    row["TeacherInnerID"] = rows[0][2];
                }
            }
            #endregion

            return Manager.BuildJson(result);
        }
        public string Teacher_StatHourly(Guid teacherId, Dictionary<string, object> data)
        {
            Command.Parameters.Clear();
            Command.Parameters.Add("@" + Alias.Order.TeacherID, MapType(Alias.Order.TeacherID)).Value = teacherId;
            if (data.ContainsKey("IsFinished") && !String.IsNullOrEmpty(data["IsFinished"].ToString()))
                Command.Parameters.Add("@IsFinished", MapType(Alias.Order.IsFinished)).Value = data["IsFinished"];
            if (data.ContainsKey("IsArchived") && !String.IsNullOrEmpty(data["IsArchived"].ToString()))
                Command.Parameters.Add("@IsArchived", MapType(Alias.Order.IsArchived)).Value = data["IsArchived"];
            if (data.ContainsKey("IsCanceled") && !String.IsNullOrEmpty(data["IsCanceled"].ToString()))
                Command.Parameters.Add("@IsCanceled", MapType(Alias.Order.IsCanceled)).Value = data["IsCanceled"];
            if (data.ContainsKey("IsPayed") && !String.IsNullOrEmpty(data["IsPayed"].ToString()))
                Command.Parameters.Add("@IsPayed", MapType(Alias.Order.IsPayed)).Value = data["IsPayed"];
            if (data.ContainsKey("IsDelivered") && !String.IsNullOrEmpty(data["IsDelivered"].ToString()))
                Command.Parameters.Add("@IsDelivered", MapType(Alias.Order.IsDelivered)).Value = data["IsDelivered"];
            if (data.ContainsKey("Start") && !String.IsNullOrEmpty(data["Start"].ToString()))
                Command.Parameters.Add("@Start", MapType(Alias.Order.CreateTime)).Value = data["Start"];
            if (data.ContainsKey("End") && !String.IsNullOrEmpty(data["End"].ToString()))
                Command.Parameters.Add("@End", MapType(Alias.Order.CreateTime)).Value = data["End"];

            string sql = "SELECT cast(substring(CONVERT(varchar," + MapColumn(Alias.Order.CreateTime) + ",120), 12, 2) as int) as [Hour], COUNT(*) as [Count], SUM(" + MapColumn(Alias.Order.Sum) + ") as [Sum], SUM(" + MapColumn(Alias.Order.TeacherCommision) + ") as [TeacherCommision] FROM " + MapTable(Alias.Objects.Order) + " WHERE " + MapColumn(Alias.Order.TeacherID) + "=?";
            if (data.ContainsKey("IsFinished") && !String.IsNullOrEmpty(data["IsFinished"].ToString()))
                sql += " and " + MapColumn(Alias.Order.IsFinished) + "=?";
            if (data.ContainsKey("IsArchived") && !String.IsNullOrEmpty(data["IsArchived"].ToString()))
                sql += " and " + MapColumn(Alias.Order.IsArchived) + "=?";
            if (data.ContainsKey("IsCanceled") && !String.IsNullOrEmpty(data["IsCanceled"].ToString()))
                sql += " and " + MapColumn(Alias.Order.IsCanceled) + "=?";
            if (data.ContainsKey("IsPayed") && !String.IsNullOrEmpty(data["IsPayed"].ToString()))
                sql += " and " + MapColumn(Alias.Order.IsPayed) + "=?";
            if (data.ContainsKey("IsDelivered") && !String.IsNullOrEmpty(data["IsDelivered"].ToString()))
                sql += " and " + MapColumn(Alias.Order.IsDelivered) + "=?";
            if (data.ContainsKey("Start") && !String.IsNullOrEmpty(data["Start"].ToString()))
                sql += " and " + MapColumn(Alias.Order.CreateTime) + ">=?";
            if (data.ContainsKey("End") && !String.IsNullOrEmpty(data["End"].ToString()))
                sql += " and " + MapColumn(Alias.Order.CreateTime) + "<=?";

            sql += " GROUP BY cast(substring(CONVERT(varchar,CreateTime,120), 12, 2) as int)";
            sql += " ORDER BY [Hour] ASC";

            Command.CommandText = sql;

            OleDbDataAdapter adapter = new OleDbDataAdapter();
            adapter.SelectCommand = Command;
            DataTable result = new DataTable();
            adapter.Fill(result);

            return Manager.BuildJson(result);
        }
        public string Teacher_StatDaily(Guid teacherId, Dictionary<string, object> data)
        {
            Command.Parameters.Clear();
            Command.Parameters.Add("@" + Alias.Order.TeacherID, MapType(Alias.Order.TeacherID)).Value = teacherId;
            if (data.ContainsKey("IsFinished") && !String.IsNullOrEmpty(data["IsFinished"].ToString()))
                Command.Parameters.Add("@IsFinished", MapType(Alias.Order.IsFinished)).Value = data["IsFinished"];
            if (data.ContainsKey("IsArchived") && !String.IsNullOrEmpty(data["IsArchived"].ToString()))
                Command.Parameters.Add("@IsArchived", MapType(Alias.Order.IsArchived)).Value = data["IsArchived"];
            if (data.ContainsKey("IsCanceled") && !String.IsNullOrEmpty(data["IsCanceled"].ToString()))
                Command.Parameters.Add("@IsCanceled", MapType(Alias.Order.IsCanceled)).Value = data["IsCanceled"];
            if (data.ContainsKey("IsPayed") && !String.IsNullOrEmpty(data["IsPayed"].ToString()))
                Command.Parameters.Add("@IsPayed", MapType(Alias.Order.IsPayed)).Value = data["IsPayed"];
            if (data.ContainsKey("IsDelivered") && !String.IsNullOrEmpty(data["IsDelivered"].ToString()))
                Command.Parameters.Add("@IsDelivered", MapType(Alias.Order.IsDelivered)).Value = data["IsDelivered"];
            if (data.ContainsKey("Start") && !String.IsNullOrEmpty(data["Start"].ToString()))
                Command.Parameters.Add("@Start", MapType(Alias.Order.CreateTime)).Value = data["Start"];
            if (data.ContainsKey("End") && !String.IsNullOrEmpty(data["End"].ToString()))
                Command.Parameters.Add("@End", MapType(Alias.Order.CreateTime)).Value = data["End"];

            string sql = "SELECT CONVERT(varchar(10) , CreateTime, 23) as [Day], COUNT(*) as [Count], SUM(" + MapColumn(Alias.Order.Sum) + ") as [Sum], SUM(" + MapColumn(Alias.Order.TeacherCommision) + ") as [TeacherCommision] FROM " + MapTable(Alias.Objects.Order) + " WHERE " + MapColumn(Alias.Order.TeacherID) + "=?";
            if (data.ContainsKey("IsFinished") && !String.IsNullOrEmpty(data["IsFinished"].ToString()))
                sql += " and " + MapColumn(Alias.Order.IsFinished) + "=?";
            if (data.ContainsKey("IsArchived") && !String.IsNullOrEmpty(data["IsArchived"].ToString()))
                sql += " and " + MapColumn(Alias.Order.IsArchived) + "=?";
            if (data.ContainsKey("IsCanceled") && !String.IsNullOrEmpty(data["IsCanceled"].ToString()))
                sql += " and " + MapColumn(Alias.Order.IsCanceled) + "=?";
            if (data.ContainsKey("IsPayed") && !String.IsNullOrEmpty(data["IsPayed"].ToString()))
                sql += " and " + MapColumn(Alias.Order.IsPayed) + "=?";
            if (data.ContainsKey("IsDelivered") && !String.IsNullOrEmpty(data["IsDelivered"].ToString()))
                sql += " and " + MapColumn(Alias.Order.IsDelivered) + "=?";
            if (data.ContainsKey("Start") && !String.IsNullOrEmpty(data["Start"].ToString()))
                sql += " and " + MapColumn(Alias.Order.CreateTime) + ">=?";
            if (data.ContainsKey("End") && !String.IsNullOrEmpty(data["End"].ToString()))
                sql += " and " + MapColumn(Alias.Order.CreateTime) + "<=?";

            sql += " GROUP BY CONVERT(varchar(10) , CreateTime, 23)";
            sql += " ORDER BY [Day] ASC";

            Command.CommandText = sql;

            OleDbDataAdapter adapter = new OleDbDataAdapter();
            adapter.SelectCommand = Command;
            DataTable result = new DataTable();
            adapter.Fill(result);

            return Manager.BuildJson(result);
        }
        public string Teacher_StatMonthly(Guid teacherId, Dictionary<string, object> data)
        {
            Command.Parameters.Clear();
            Command.Parameters.Add("@" + Alias.Order.TeacherID, MapType(Alias.Order.TeacherID)).Value = teacherId;
            if (data.ContainsKey("IsFinished") && !String.IsNullOrEmpty(data["IsFinished"].ToString()))
                Command.Parameters.Add("@IsFinished", MapType(Alias.Order.IsFinished)).Value = data["IsFinished"];
            if (data.ContainsKey("IsArchived") && !String.IsNullOrEmpty(data["IsArchived"].ToString()))
                Command.Parameters.Add("@IsArchived", MapType(Alias.Order.IsArchived)).Value = data["IsArchived"];
            if (data.ContainsKey("IsCanceled") && !String.IsNullOrEmpty(data["IsCanceled"].ToString()))
                Command.Parameters.Add("@IsCanceled", MapType(Alias.Order.IsCanceled)).Value = data["IsCanceled"];
            if (data.ContainsKey("IsPayed") && !String.IsNullOrEmpty(data["IsPayed"].ToString()))
                Command.Parameters.Add("@IsPayed", MapType(Alias.Order.IsPayed)).Value = data["IsPayed"];
            if (data.ContainsKey("IsDelivered") && !String.IsNullOrEmpty(data["IsDelivered"].ToString()))
                Command.Parameters.Add("@IsDelivered", MapType(Alias.Order.IsDelivered)).Value = data["IsDelivered"];
            if (data.ContainsKey("Start") && !String.IsNullOrEmpty(data["Start"].ToString()))
                Command.Parameters.Add("@Start", MapType(Alias.Order.CreateTime)).Value = data["Start"];
            if (data.ContainsKey("End") && !String.IsNullOrEmpty(data["End"].ToString()))
                Command.Parameters.Add("@End", MapType(Alias.Order.CreateTime)).Value = data["End"];

            string sql = "SELECT CONVERT(varchar(7) , CreateTime, 23) as [Month], COUNT(*) as [Count], SUM(" + MapColumn(Alias.Order.Sum) + ") as [Sum], SUM(" + MapColumn(Alias.Order.TeacherCommision) + ") as [TeacherCommision] FROM " + MapTable(Alias.Objects.Order) + " WHERE " + MapColumn(Alias.Order.TeacherID) + "=?";
            if (data.ContainsKey("IsFinished") && !String.IsNullOrEmpty(data["IsFinished"].ToString()))
                sql += " and " + MapColumn(Alias.Order.IsFinished) + "=?";
            if (data.ContainsKey("IsArchived") && !String.IsNullOrEmpty(data["IsArchived"].ToString()))
                sql += " and " + MapColumn(Alias.Order.IsArchived) + "=?";
            if (data.ContainsKey("IsCanceled") && !String.IsNullOrEmpty(data["IsCanceled"].ToString()))
                sql += " and " + MapColumn(Alias.Order.IsCanceled) + "=?";
            if (data.ContainsKey("IsPayed") && !String.IsNullOrEmpty(data["IsPayed"].ToString()))
                sql += " and " + MapColumn(Alias.Order.IsPayed) + "=?";
            if (data.ContainsKey("IsDelivered") && !String.IsNullOrEmpty(data["IsDelivered"].ToString()))
                sql += " and " + MapColumn(Alias.Order.IsDelivered) + "=?";
            if (data.ContainsKey("Start") && !String.IsNullOrEmpty(data["Start"].ToString()))
                sql += " and " + MapColumn(Alias.Order.CreateTime) + ">=?";
            if (data.ContainsKey("End") && !String.IsNullOrEmpty(data["End"].ToString()))
                sql += " and " + MapColumn(Alias.Order.CreateTime) + "<=?";

            sql += " GROUP BY CONVERT(varchar(7) , CreateTime, 23)";
            sql += " ORDER BY [Month] ASC";

            Command.CommandText = sql;

            OleDbDataAdapter adapter = new OleDbDataAdapter();
            adapter.SelectCommand = Command;
            DataTable result = new DataTable();
            adapter.Fill(result);

            return Manager.BuildJson(result);
        }
        #endregion

        #region 课程统计
        public string Course_StatOne(Guid courseId, Dictionary<string, object> data)
        {
            Command.Parameters.Clear();
            Command.Parameters.Add("@" + Alias.Order.CourseID, MapType(Alias.Order.CourseID)).Value = courseId;
            if (data.ContainsKey("IsFinished") && !String.IsNullOrEmpty(data["IsFinished"].ToString()))
                Command.Parameters.Add("@IsFinished", MapType(Alias.Order.IsFinished)).Value = data["IsFinished"];
            if (data.ContainsKey("IsArchived") && !String.IsNullOrEmpty(data["IsArchived"].ToString()))
                Command.Parameters.Add("@IsArchived", MapType(Alias.Order.IsArchived)).Value = data["IsArchived"];
            if (data.ContainsKey("IsCanceled") && !String.IsNullOrEmpty(data["IsCanceled"].ToString()))
                Command.Parameters.Add("@IsCanceled", MapType(Alias.Order.IsCanceled)).Value = data["IsCanceled"];
            if (data.ContainsKey("IsPayed") && !String.IsNullOrEmpty(data["IsPayed"].ToString()))
                Command.Parameters.Add("@IsPayed", MapType(Alias.Order.IsPayed)).Value = data["IsPayed"];
            if (data.ContainsKey("IsDelivered") && !String.IsNullOrEmpty(data["IsDelivered"].ToString()))
                Command.Parameters.Add("@IsDelivered", MapType(Alias.Order.IsDelivered)).Value = data["IsDelivered"];
            if (data.ContainsKey("Start") && !String.IsNullOrEmpty(data["Start"].ToString()))
                Command.Parameters.Add("@Start", MapType(Alias.Order.CreateTime)).Value = data["Start"];
            if (data.ContainsKey("End") && !String.IsNullOrEmpty(data["End"].ToString()))
                Command.Parameters.Add("@End", MapType(Alias.Order.CreateTime)).Value = data["End"];

            string sql = "SELECT COUNT(*) AS [Count], SUM(" + MapColumn(Alias.Order.Sum) + ") as [Sum], SUM(" + MapColumn(Alias.Order.AgentCommision) + ") as [AgentCommision], SUM(" + MapColumn(Alias.Order.TeacherCommision) + ") as [TeacherCommision] FROM " + MapTable(Alias.Objects.Order) + " WHERE " + MapColumn(Alias.Order.CourseID) + "=?";
            if (data.ContainsKey("IsFinished") && !String.IsNullOrEmpty(data["IsFinished"].ToString()))
                sql += " and " + MapColumn(Alias.Order.IsFinished) + "=?";
            if (data.ContainsKey("IsArchived") && !String.IsNullOrEmpty(data["IsArchived"].ToString()))
                sql += " and " + MapColumn(Alias.Order.IsArchived) + "=?";
            if (data.ContainsKey("IsCanceled") && !String.IsNullOrEmpty(data["IsCanceled"].ToString()))
                sql += " and " + MapColumn(Alias.Order.IsCanceled) + "=?";
            if (data.ContainsKey("IsPayed") && !String.IsNullOrEmpty(data["IsPayed"].ToString()))
                sql += " and " + MapColumn(Alias.Order.IsPayed) + "=?";
            if (data.ContainsKey("IsDelivered") && !String.IsNullOrEmpty(data["IsDelivered"].ToString()))
                sql += " and " + MapColumn(Alias.Order.IsDelivered) + "=?";
            if (data.ContainsKey("Start") && !String.IsNullOrEmpty(data["Start"].ToString()))
                sql += " and " + MapColumn(Alias.Order.CreateTime) + ">=?";
            if (data.ContainsKey("End") && !String.IsNullOrEmpty(data["End"].ToString()))
                sql += " and " + MapColumn(Alias.Order.CreateTime) + "<=?";

            Command.CommandText = sql;

            string result;
            DataReader = Command.ExecuteReader();
            if (DataReader.Read())
            {
                result = "{\"Count\":" + DataReader["Count"] + ",\"Sum\":" + DataReader["Sum"] + ",\"AgentCommision\":" + DataReader["AgentCommision"] + ",\"TeacherCommision\":" + DataReader["TeacherCommision"] + "}";
            }
            else
            {
                result = "{\"Count\":0,\"Sum\":0,\"AgentCommision\":0,\"TeacherCommision\":0}";
            }
            DataReader.Close();

            return result;
        }
        public string Course_StatAll(Dictionary<string, object> data)
        {
            Command.Parameters.Clear();
            if (data.ContainsKey("IsFinished") && !String.IsNullOrEmpty(data["IsFinished"].ToString()))
                Command.Parameters.Add("@IsFinished", MapType(Alias.Order.IsFinished)).Value = data["IsFinished"];
            if (data.ContainsKey("IsArchived") && !String.IsNullOrEmpty(data["IsArchived"].ToString()))
                Command.Parameters.Add("@IsArchived", MapType(Alias.Order.IsArchived)).Value = data["IsArchived"];
            if (data.ContainsKey("IsCanceled") && !String.IsNullOrEmpty(data["IsCanceled"].ToString()))
                Command.Parameters.Add("@IsCanceled", MapType(Alias.Order.IsCanceled)).Value = data["IsCanceled"];
            if (data.ContainsKey("IsPayed") && !String.IsNullOrEmpty(data["IsPayed"].ToString()))
                Command.Parameters.Add("@IsPayed", MapType(Alias.Order.IsPayed)).Value = data["IsPayed"];
            if (data.ContainsKey("IsDelivered") && !String.IsNullOrEmpty(data["IsDelivered"].ToString()))
                Command.Parameters.Add("@IsDelivered", MapType(Alias.Order.IsDelivered)).Value = data["IsDelivered"];
            if (data.ContainsKey("Start") && !String.IsNullOrEmpty(data["Start"].ToString()))
                Command.Parameters.Add("@Start", MapType(Alias.Order.CreateTime)).Value = data["Start"];
            if (data.ContainsKey("End") && !String.IsNullOrEmpty(data["End"].ToString()))
                Command.Parameters.Add("@End", MapType(Alias.Order.CreateTime)).Value = data["End"];

            string sql = "SELECT CourseID, COUNT(*) AS [Count], SUM(" + MapColumn(Alias.Order.Sum) + ") as [Sum], SUM(" + MapColumn(Alias.Order.AgentCommision) + ") as [AgentCommision], SUM(" + MapColumn(Alias.Order.TeacherCommision) + ") as [TeacherCommision] FROM " + MapTable(Alias.Objects.Order);

            string and = "";
            if (data.ContainsKey("IsFinished") && !String.IsNullOrEmpty(data["IsFinished"].ToString()))
            {
                if (and == "")
                    sql += " WHERE ";

                sql += and + MapColumn(Alias.Order.IsFinished) + "=?";
                and = " and ";
            }
            if (data.ContainsKey("IsArchived") && !String.IsNullOrEmpty(data["IsArchived"].ToString()))
            {
                if (and == "")
                    sql += " WHERE ";

                sql += and + MapColumn(Alias.Order.IsArchived) + "=?";
                and = " and ";
            }
            if (data.ContainsKey("IsCanceled") && !String.IsNullOrEmpty(data["IsCanceled"].ToString()))
            {
                if (and == "")
                    sql += " WHERE ";

                sql += and + MapColumn(Alias.Order.IsCanceled) + "=?";
                and = " and ";
            }
            if (data.ContainsKey("IsPayed") && !String.IsNullOrEmpty(data["IsPayed"].ToString()))
            {
                if (and == "")
                    sql += " WHERE ";

                sql += and + MapColumn(Alias.Order.IsPayed) + "=?";
                and = " and ";
            }
            if (data.ContainsKey("IsDelivered") && !String.IsNullOrEmpty(data["IsDelivered"].ToString()))
            {
                if (and == "")
                    sql += " WHERE ";

                sql += and + MapColumn(Alias.Order.IsDelivered) + "=?";
                and = " and ";
            }
            if (data.ContainsKey("Start") && !String.IsNullOrEmpty(data["Start"].ToString()))
            {
                if (and == "")
                    sql += " WHERE ";

                sql += and + MapColumn(Alias.Order.CreateTime) + ">=?";
                and = " and ";
            }
            if (data.ContainsKey("End") && !String.IsNullOrEmpty(data["End"].ToString()))
            {
                if (and == "")
                    sql += " WHERE ";

                sql += and + MapColumn(Alias.Order.CreateTime) + "<=?";
            }

            sql += " GROUP BY CourseID";
            sql += " ORDER BY [Count] DESC";

            Command.CommandText = sql;

            OleDbDataAdapter adapter = new OleDbDataAdapter();
            adapter.SelectCommand = Command;
            DataTable result = new DataTable();
            adapter.Fill(result);

            #region 增加课程名称

            #region 课程列表
            Command.Parameters.Clear();
            Command.CommandText = "SELECT " + MapColumn(Alias.Course.ID) + "," + MapColumn(Alias.Course.InnerID) + "," + MapColumn(Alias.Course.Name, Lang) + " FROM " + MapTable(Alias.Objects.Course);
            DataTable courseList = new DataTable();
            adapter.Fill(courseList);
            #endregion

            result.Columns.Add("CourseInnerID", MapCSType(Alias.Course.InnerID));
            result.Columns.Add("CourseName", MapCSType(Alias.Course.Name));
            foreach (DataRow row in result.Rows)
            {
                DataRow[] rows = courseList.Select("ID='" + row["CourseID"] + "'");
                if (rows.Length > 0)
                {
                    row["CourseInnerID"] = rows[0][1];
                    row["CourseName"] = rows[0][2];
                }
            }
            #endregion

            return Manager.BuildJson(result);
        }
        public string Course_StatHourly(Guid courseId, Dictionary<string, object> data)
        {
            Command.Parameters.Clear();
            Command.Parameters.Add("@" + Alias.Order.CourseID, MapType(Alias.Order.CourseID)).Value = courseId;
            if (data.ContainsKey("IsFinished") && !String.IsNullOrEmpty(data["IsFinished"].ToString()))
                Command.Parameters.Add("@IsFinished", MapType(Alias.Order.IsFinished)).Value = data["IsFinished"];
            if (data.ContainsKey("IsArchived") && !String.IsNullOrEmpty(data["IsArchived"].ToString()))
                Command.Parameters.Add("@IsArchived", MapType(Alias.Order.IsArchived)).Value = data["IsArchived"];
            if (data.ContainsKey("IsCanceled") && !String.IsNullOrEmpty(data["IsCanceled"].ToString()))
                Command.Parameters.Add("@IsCanceled", MapType(Alias.Order.IsCanceled)).Value = data["IsCanceled"];
            if (data.ContainsKey("IsPayed") && !String.IsNullOrEmpty(data["IsPayed"].ToString()))
                Command.Parameters.Add("@IsPayed", MapType(Alias.Order.IsPayed)).Value = data["IsPayed"];
            if (data.ContainsKey("IsDelivered") && !String.IsNullOrEmpty(data["IsDelivered"].ToString()))
                Command.Parameters.Add("@IsDelivered", MapType(Alias.Order.IsDelivered)).Value = data["IsDelivered"];
            if (data.ContainsKey("Start") && !String.IsNullOrEmpty(data["Start"].ToString()))
                Command.Parameters.Add("@Start", MapType(Alias.Order.CreateTime)).Value = data["Start"];
            if (data.ContainsKey("End") && !String.IsNullOrEmpty(data["End"].ToString()))
                Command.Parameters.Add("@End", MapType(Alias.Order.CreateTime)).Value = data["End"];

            string sql = "SELECT cast(substring(CONVERT(varchar," + MapColumn(Alias.Order.CreateTime) + ",120), 12, 2) as int) as [Hour], COUNT(*) AS [Count], SUM(" + MapColumn(Alias.Order.Sum) + ") as [Sum], SUM(" + MapColumn(Alias.Order.AgentCommision) + ") as [AgentCommision], SUM(" + MapColumn(Alias.Order.TeacherCommision) + ") as [TeacherCommision] FROM " + MapTable(Alias.Objects.Order) + " WHERE " + MapColumn(Alias.Order.CourseID) + "=?";
            if (data.ContainsKey("IsFinished") && !String.IsNullOrEmpty(data["IsFinished"].ToString()))
                sql += " and " + MapColumn(Alias.Order.IsFinished) + "=?";
            if (data.ContainsKey("IsArchived") && !String.IsNullOrEmpty(data["IsArchived"].ToString()))
                sql += " and " + MapColumn(Alias.Order.IsArchived) + "=?";
            if (data.ContainsKey("IsCanceled") && !String.IsNullOrEmpty(data["IsCanceled"].ToString()))
                sql += " and " + MapColumn(Alias.Order.IsCanceled) + "=?";
            if (data.ContainsKey("IsPayed") && !String.IsNullOrEmpty(data["IsPayed"].ToString()))
                sql += " and " + MapColumn(Alias.Order.IsPayed) + "=?";
            if (data.ContainsKey("IsDelivered") && !String.IsNullOrEmpty(data["IsDelivered"].ToString()))
                sql += " and " + MapColumn(Alias.Order.IsDelivered) + "=?";
            if (data.ContainsKey("Start") && !String.IsNullOrEmpty(data["Start"].ToString()))
                sql += " and " + MapColumn(Alias.Order.CreateTime) + ">=?";
            if (data.ContainsKey("End") && !String.IsNullOrEmpty(data["End"].ToString()))
                sql += " and " + MapColumn(Alias.Order.CreateTime) + "<=?";

            sql += " GROUP BY cast(substring(CONVERT(varchar,CreateTime,120), 12, 2) as int)";
            sql += " ORDER BY [Hour] ASC";

            Command.CommandText = sql;

            OleDbDataAdapter adapter = new OleDbDataAdapter();
            adapter.SelectCommand = Command;
            DataTable result = new DataTable();
            adapter.Fill(result);

            return Manager.BuildJson(result);
        }
        public string Course_StatDaily(Guid courseId, Dictionary<string, object> data)
        {
            Command.Parameters.Clear();
            Command.Parameters.Add("@" + Alias.Order.CourseID, MapType(Alias.Order.CourseID)).Value = courseId;
            if (data.ContainsKey("IsFinished") && !String.IsNullOrEmpty(data["IsFinished"].ToString()))
                Command.Parameters.Add("@IsFinished", MapType(Alias.Order.IsFinished)).Value = data["IsFinished"];
            if (data.ContainsKey("IsArchived") && !String.IsNullOrEmpty(data["IsArchived"].ToString()))
                Command.Parameters.Add("@IsArchived", MapType(Alias.Order.IsArchived)).Value = data["IsArchived"];
            if (data.ContainsKey("IsCanceled") && !String.IsNullOrEmpty(data["IsCanceled"].ToString()))
                Command.Parameters.Add("@IsCanceled", MapType(Alias.Order.IsCanceled)).Value = data["IsCanceled"];
            if (data.ContainsKey("IsPayed") && !String.IsNullOrEmpty(data["IsPayed"].ToString()))
                Command.Parameters.Add("@IsPayed", MapType(Alias.Order.IsPayed)).Value = data["IsPayed"];
            if (data.ContainsKey("IsDelivered") && !String.IsNullOrEmpty(data["IsDelivered"].ToString()))
                Command.Parameters.Add("@IsDelivered", MapType(Alias.Order.IsDelivered)).Value = data["IsDelivered"];
            if (data.ContainsKey("Start") && !String.IsNullOrEmpty(data["Start"].ToString()))
                Command.Parameters.Add("@Start", MapType(Alias.Order.CreateTime)).Value = data["Start"];
            if (data.ContainsKey("End") && !String.IsNullOrEmpty(data["End"].ToString()))
                Command.Parameters.Add("@End", MapType(Alias.Order.CreateTime)).Value = data["End"];

            string sql = "SELECT CONVERT(varchar(10) , CreateTime, 23) as [Day], COUNT(*) AS [Count], SUM(" + MapColumn(Alias.Order.Sum) + ") as [Sum], SUM(" + MapColumn(Alias.Order.AgentCommision) + ") as [AgentCommision], SUM(" + MapColumn(Alias.Order.TeacherCommision) + ") as [TeacherCommision] FROM " + MapTable(Alias.Objects.Order) + " WHERE " + MapColumn(Alias.Order.CourseID) + "=?";
            if (data.ContainsKey("IsFinished") && !String.IsNullOrEmpty(data["IsFinished"].ToString()))
                sql += " and " + MapColumn(Alias.Order.IsFinished) + "=?";
            if (data.ContainsKey("IsArchived") && !String.IsNullOrEmpty(data["IsArchived"].ToString()))
                sql += " and " + MapColumn(Alias.Order.IsArchived) + "=?";
            if (data.ContainsKey("IsCanceled") && !String.IsNullOrEmpty(data["IsCanceled"].ToString()))
                sql += " and " + MapColumn(Alias.Order.IsCanceled) + "=?";
            if (data.ContainsKey("IsPayed") && !String.IsNullOrEmpty(data["IsPayed"].ToString()))
                sql += " and " + MapColumn(Alias.Order.IsPayed) + "=?";
            if (data.ContainsKey("IsDelivered") && !String.IsNullOrEmpty(data["IsDelivered"].ToString()))
                sql += " and " + MapColumn(Alias.Order.IsDelivered) + "=?";
            if (data.ContainsKey("Start") && !String.IsNullOrEmpty(data["Start"].ToString()))
                sql += " and " + MapColumn(Alias.Order.CreateTime) + ">=?";
            if (data.ContainsKey("End") && !String.IsNullOrEmpty(data["End"].ToString()))
                sql += " and " + MapColumn(Alias.Order.CreateTime) + "<=?";

            sql += " GROUP BY CONVERT(varchar(10) , CreateTime, 23)";
            sql += " ORDER BY [Day] ASC";

            Command.CommandText = sql;

            OleDbDataAdapter adapter = new OleDbDataAdapter();
            adapter.SelectCommand = Command;
            DataTable result = new DataTable();
            adapter.Fill(result);

            return Manager.BuildJson(result);
        }
        public string Course_StatMonthly(Guid courseId, Dictionary<string, object> data)
        {
            Command.Parameters.Clear();
            Command.Parameters.Add("@" + Alias.Order.CourseID, MapType(Alias.Order.CourseID)).Value = courseId;
            if (data.ContainsKey("IsFinished") && !String.IsNullOrEmpty(data["IsFinished"].ToString()))
                Command.Parameters.Add("@IsFinished", MapType(Alias.Order.IsFinished)).Value = data["IsFinished"];
            if (data.ContainsKey("IsArchived") && !String.IsNullOrEmpty(data["IsArchived"].ToString()))
                Command.Parameters.Add("@IsArchived", MapType(Alias.Order.IsArchived)).Value = data["IsArchived"];
            if (data.ContainsKey("IsCanceled") && !String.IsNullOrEmpty(data["IsCanceled"].ToString()))
                Command.Parameters.Add("@IsCanceled", MapType(Alias.Order.IsCanceled)).Value = data["IsCanceled"];
            if (data.ContainsKey("IsPayed") && !String.IsNullOrEmpty(data["IsPayed"].ToString()))
                Command.Parameters.Add("@IsPayed", MapType(Alias.Order.IsPayed)).Value = data["IsPayed"];
            if (data.ContainsKey("IsDelivered") && !String.IsNullOrEmpty(data["IsDelivered"].ToString()))
                Command.Parameters.Add("@IsDelivered", MapType(Alias.Order.IsDelivered)).Value = data["IsDelivered"];
            if (data.ContainsKey("Start") && !String.IsNullOrEmpty(data["Start"].ToString()))
                Command.Parameters.Add("@Start", MapType(Alias.Order.CreateTime)).Value = data["Start"];
            if (data.ContainsKey("End") && !String.IsNullOrEmpty(data["End"].ToString()))
                Command.Parameters.Add("@End", MapType(Alias.Order.CreateTime)).Value = data["End"];

            string sql = "SELECT CONVERT(varchar(7) , CreateTime, 23) as [Month], COUNT(*) AS [Count], SUM(" + MapColumn(Alias.Order.Sum) + ") as [Sum], SUM(" + MapColumn(Alias.Order.AgentCommision) + ") as [AgentCommision], SUM(" + MapColumn(Alias.Order.TeacherCommision) + ") as [TeacherCommision] FROM " + MapTable(Alias.Objects.Order) + " WHERE " + MapColumn(Alias.Order.CourseID) + "=?";
            if (data.ContainsKey("IsFinished") && !String.IsNullOrEmpty(data["IsFinished"].ToString()))
                sql += " and " + MapColumn(Alias.Order.IsFinished) + "=?";
            if (data.ContainsKey("IsArchived") && !String.IsNullOrEmpty(data["IsArchived"].ToString()))
                sql += " and " + MapColumn(Alias.Order.IsArchived) + "=?";
            if (data.ContainsKey("IsCanceled") && !String.IsNullOrEmpty(data["IsCanceled"].ToString()))
                sql += " and " + MapColumn(Alias.Order.IsCanceled) + "=?";
            if (data.ContainsKey("IsPayed") && !String.IsNullOrEmpty(data["IsPayed"].ToString()))
                sql += " and " + MapColumn(Alias.Order.IsPayed) + "=?";
            if (data.ContainsKey("IsDelivered") && !String.IsNullOrEmpty(data["IsDelivered"].ToString()))
                sql += " and " + MapColumn(Alias.Order.IsDelivered) + "=?";
            if (data.ContainsKey("Start") && !String.IsNullOrEmpty(data["Start"].ToString()))
                sql += " and " + MapColumn(Alias.Order.CreateTime) + ">=?";
            if (data.ContainsKey("End") && !String.IsNullOrEmpty(data["End"].ToString()))
                sql += " and " + MapColumn(Alias.Order.CreateTime) + "<=?";

            sql += " GROUP BY CONVERT(varchar(7) , CreateTime, 23)";
            sql += " ORDER BY [Month] ASC";

            Command.CommandText = sql;

            OleDbDataAdapter adapter = new OleDbDataAdapter();
            adapter.SelectCommand = Command;
            DataTable result = new DataTable();
            adapter.Fill(result);

            return Manager.BuildJson(result);
        }
        #endregion

        #region 代理统计
        public string Agent_StatOne(Guid agentId, Dictionary<string, object> data)
        {
            Command.Parameters.Clear();
            Command.Parameters.Add("@" + Alias.Order.DistributerIsAgent, MapType(Alias.Order.DistributerIsAgent)).Value = true;
            Command.Parameters.Add("@" + Alias.Order.DistributerID, MapType(Alias.Order.DistributerID)).Value = agentId;

            if (data.ContainsKey("IsFinished") && !String.IsNullOrEmpty(data["IsFinished"].ToString()))
                Command.Parameters.Add("@IsFinished", MapType(Alias.Order.IsFinished)).Value = data["IsFinished"];
            if (data.ContainsKey("IsArchived") && !String.IsNullOrEmpty(data["IsArchived"].ToString()))
                Command.Parameters.Add("@IsArchived", MapType(Alias.Order.IsArchived)).Value = data["IsArchived"];
            if (data.ContainsKey("IsCanceled") && !String.IsNullOrEmpty(data["IsCanceled"].ToString()))
                Command.Parameters.Add("@IsCanceled", MapType(Alias.Order.IsCanceled)).Value = data["IsCanceled"];
            if (data.ContainsKey("IsPayed") && !String.IsNullOrEmpty(data["IsPayed"].ToString()))
                Command.Parameters.Add("@IsPayed", MapType(Alias.Order.IsPayed)).Value = data["IsPayed"];
            if (data.ContainsKey("IsDelivered") && !String.IsNullOrEmpty(data["IsDelivered"].ToString()))
                Command.Parameters.Add("@IsDelivered", MapType(Alias.Order.IsDelivered)).Value = data["IsDelivered"];
            if (data.ContainsKey("Start") && !String.IsNullOrEmpty(data["Start"].ToString()))
                Command.Parameters.Add("@Start", MapType(Alias.Order.CreateTime)).Value = data["Start"];
            if (data.ContainsKey("End") && !String.IsNullOrEmpty(data["End"].ToString()))
                Command.Parameters.Add("@End", MapType(Alias.Order.CreateTime)).Value = data["End"];

            string sql = "SELECT COUNT(*) AS [Count], SUM(" + MapColumn(Alias.Order.Sum) + ") as [Sum] FROM " + MapTable(Alias.Objects.Order) + " WHERE " + MapColumn(Alias.Order.DistributerIsAgent) + "=? and " + MapColumn(Alias.Order.DistributerID) + "=?";

            if (data.ContainsKey("IsFinished") && !String.IsNullOrEmpty(data["IsFinished"].ToString()))
                sql += " and " + MapColumn(Alias.Order.IsFinished) + "=?";
            if (data.ContainsKey("IsArchived") && !String.IsNullOrEmpty(data["IsArchived"].ToString()))
                sql += " and " + MapColumn(Alias.Order.IsArchived) + "=?";
            if (data.ContainsKey("IsCanceled") && !String.IsNullOrEmpty(data["IsCanceled"].ToString()))
                sql += " and " + MapColumn(Alias.Order.IsCanceled) + "=?";
            if (data.ContainsKey("IsPayed") && !String.IsNullOrEmpty(data["IsPayed"].ToString()))
                sql += " and " + MapColumn(Alias.Order.IsPayed) + "=?";
            if (data.ContainsKey("IsDelivered") && !String.IsNullOrEmpty(data["IsDelivered"].ToString()))
                sql += " and " + MapColumn(Alias.Order.IsDelivered) + "=?";
            if (data.ContainsKey("Start") && !String.IsNullOrEmpty(data["Start"].ToString()))
                sql += " and " + MapColumn(Alias.Order.CreateTime) + ">=?";
            if (data.ContainsKey("End") && !String.IsNullOrEmpty(data["End"].ToString()))
                sql += " and " + MapColumn(Alias.Order.CreateTime) + "<=?";

            Command.CommandText = sql;

            string result;
            DataReader = Command.ExecuteReader();
            if (DataReader.Read())
            {
                result = "{\"Count\":" + DataReader["Count"] + ",\"Sum\":" + DataReader["Sum"] + "}";
            }
            else
            {
                result = "{\"Count\":0,\"Sum\":0}";
            }
            DataReader.Close();

            return result;
        }
        public string Agent_StatAll(Dictionary<string, object> data)
        {
            Command.Parameters.Clear();
            Command.Parameters.Add("@" + Alias.Order.DistributerIsAgent, MapType(Alias.Order.DistributerIsAgent)).Value = true;
            if (data.ContainsKey("IsFinished") && !String.IsNullOrEmpty(data["IsFinished"].ToString()))
                Command.Parameters.Add("@IsFinished", MapType(Alias.Order.IsFinished)).Value = data["IsFinished"];
            if (data.ContainsKey("IsArchived") && !String.IsNullOrEmpty(data["IsArchived"].ToString()))
                Command.Parameters.Add("@IsArchived", MapType(Alias.Order.IsArchived)).Value = data["IsArchived"];
            if (data.ContainsKey("IsCanceled") && !String.IsNullOrEmpty(data["IsCanceled"].ToString()))
                Command.Parameters.Add("@IsCanceled", MapType(Alias.Order.IsCanceled)).Value = data["IsCanceled"];
            if (data.ContainsKey("IsPayed") && !String.IsNullOrEmpty(data["IsPayed"].ToString()))
                Command.Parameters.Add("@IsPayed", MapType(Alias.Order.IsPayed)).Value = data["IsPayed"];
            if (data.ContainsKey("IsDelivered") && !String.IsNullOrEmpty(data["IsDelivered"].ToString()))
                Command.Parameters.Add("@IsDelivered", MapType(Alias.Order.IsDelivered)).Value = data["IsDelivered"];
            if (data.ContainsKey("Start") && !String.IsNullOrEmpty(data["Start"].ToString()))
                Command.Parameters.Add("@Start", MapType(Alias.Order.CreateTime)).Value = data["Start"];
            if (data.ContainsKey("End") && !String.IsNullOrEmpty(data["End"].ToString()))
                Command.Parameters.Add("@End", MapType(Alias.Order.CreateTime)).Value = data["End"];

            string group = MapColumn(Alias.Order.DistributerID);
            string sql = "SELECT " + group + " as [AgentID], COUNT(*) as [Count], SUM(" + MapColumn(Alias.Order.Sum) + ") as [Sum] FROM " + MapTable(Alias.Objects.Order) + " WHERE " + MapColumn(Alias.Order.DistributerIsAgent) + "=?";
            if (data.ContainsKey("IsFinished") && !String.IsNullOrEmpty(data["IsFinished"].ToString()))
                sql += " and " + MapColumn(Alias.Order.IsFinished) + "=?";
            if (data.ContainsKey("IsArchived") && !String.IsNullOrEmpty(data["IsArchived"].ToString()))
                sql += " and " + MapColumn(Alias.Order.IsArchived) + "=?";
            if (data.ContainsKey("IsCanceled") && !String.IsNullOrEmpty(data["IsCanceled"].ToString()))
                sql += " and " + MapColumn(Alias.Order.IsCanceled) + "=?";
            if (data.ContainsKey("IsPayed") && !String.IsNullOrEmpty(data["IsPayed"].ToString()))
                sql += " and " + MapColumn(Alias.Order.IsPayed) + "=?";
            if (data.ContainsKey("IsDelivered") && !String.IsNullOrEmpty(data["IsDelivered"].ToString()))
                sql += " and " + MapColumn(Alias.Order.IsDelivered) + "=?";
            if (data.ContainsKey("Start") && !String.IsNullOrEmpty(data["Start"].ToString()))
                sql += " and " + MapColumn(Alias.Order.CreateTime) + ">=?";
            if (data.ContainsKey("End") && !String.IsNullOrEmpty(data["End"].ToString()))
                sql += " and " + MapColumn(Alias.Order.CreateTime) + "<=?";

            sql += " GROUP BY " + group;
            sql += " ORDER BY [Count] DESC";

            Command.CommandText = sql;

            OleDbDataAdapter adapter = new OleDbDataAdapter();
            adapter.SelectCommand = Command;
            DataTable result = new DataTable();
            adapter.Fill(result);

            #region 增加代理名称

            #region 代理列表
            Command.Parameters.Clear();
            Command.CommandText = "SELECT " + MapColumn(Alias.Member.UserID) + "," + MapColumn(Alias.Member.UserNickname) + "," + MapColumn(Alias.Member.UserHead) + " FROM " + MapTable(Alias.Objects.Member);
            DataTable list = new DataTable();
            adapter.Fill(list);
            #endregion

            result.Columns.Add("AgentNickname", MapCSType(Alias.Member.UserNickname));
            result.Columns.Add("AgentHead", MapCSType(Alias.Member.UserHead));
            foreach (DataRow row in result.Rows)
            {
                DataRow[] rows = list.Select(MapColumn(Alias.Member.UserID) + "='" + row["AgentID"] + "'");
                if (rows.Length > 0)
                {
                    row["AgentNickname"] = rows[0][1];
                    row["AgentHead"] = rows[0][2];
                }
            }
            #endregion

            return Manager.BuildJson(result);
        }
        public string Agent_StatHourly(Guid agentId, Dictionary<string, object> data)
        {
            Command.Parameters.Clear();
            Command.Parameters.Add("@" + Alias.Order.DistributerIsAgent, MapType(Alias.Order.DistributerIsAgent)).Value = true;
            Command.Parameters.Add("@" + Alias.Order.DistributerID, MapType(Alias.Order.DistributerID)).Value = agentId;
            if (data.ContainsKey("IsFinished") && !String.IsNullOrEmpty(data["IsFinished"].ToString()))
                Command.Parameters.Add("@IsFinished", MapType(Alias.Order.IsFinished)).Value = data["IsFinished"];
            if (data.ContainsKey("IsArchived") && !String.IsNullOrEmpty(data["IsArchived"].ToString()))
                Command.Parameters.Add("@IsArchived", MapType(Alias.Order.IsArchived)).Value = data["IsArchived"];
            if (data.ContainsKey("IsCanceled") && !String.IsNullOrEmpty(data["IsCanceled"].ToString()))
                Command.Parameters.Add("@IsCanceled", MapType(Alias.Order.IsCanceled)).Value = data["IsCanceled"];
            if (data.ContainsKey("IsPayed") && !String.IsNullOrEmpty(data["IsPayed"].ToString()))
                Command.Parameters.Add("@IsPayed", MapType(Alias.Order.IsPayed)).Value = data["IsPayed"];
            if (data.ContainsKey("IsDelivered") && !String.IsNullOrEmpty(data["IsDelivered"].ToString()))
                Command.Parameters.Add("@IsDelivered", MapType(Alias.Order.IsDelivered)).Value = data["IsDelivered"];
            if (data.ContainsKey("Start") && !String.IsNullOrEmpty(data["Start"].ToString()))
                Command.Parameters.Add("@Start", MapType(Alias.Order.CreateTime)).Value = data["Start"];
            if (data.ContainsKey("End") && !String.IsNullOrEmpty(data["End"].ToString()))
                Command.Parameters.Add("@End", MapType(Alias.Order.CreateTime)).Value = data["End"];

            string sql = "SELECT cast(substring(CONVERT(varchar," + MapColumn(Alias.Order.CreateTime) + ",120), 12, 2) as int) as [Hour], COUNT(*) as [Count], SUM(" + MapColumn(Alias.Order.Sum) + ") as [Sum] FROM " + MapTable(Alias.Objects.Order) + " WHERE " + MapColumn(Alias.Order.DistributerIsAgent) + "=? and " + MapColumn(Alias.Order.DistributerID) + "=?";
            if (data.ContainsKey("IsFinished") && !String.IsNullOrEmpty(data["IsFinished"].ToString()))
                sql += " and " + MapColumn(Alias.Order.IsFinished) + "=?";
            if (data.ContainsKey("IsArchived") && !String.IsNullOrEmpty(data["IsArchived"].ToString()))
                sql += " and " + MapColumn(Alias.Order.IsArchived) + "=?";
            if (data.ContainsKey("IsCanceled") && !String.IsNullOrEmpty(data["IsCanceled"].ToString()))
                sql += " and " + MapColumn(Alias.Order.IsCanceled) + "=?";
            if (data.ContainsKey("IsPayed") && !String.IsNullOrEmpty(data["IsPayed"].ToString()))
                sql += " and " + MapColumn(Alias.Order.IsPayed) + "=?";
            if (data.ContainsKey("IsDelivered") && !String.IsNullOrEmpty(data["IsDelivered"].ToString()))
                sql += " and " + MapColumn(Alias.Order.IsDelivered) + "=?";
            if (data.ContainsKey("Start") && !String.IsNullOrEmpty(data["Start"].ToString()))
                sql += " and " + MapColumn(Alias.Order.CreateTime) + ">=?";
            if (data.ContainsKey("End") && !String.IsNullOrEmpty(data["End"].ToString()))
                sql += " and " + MapColumn(Alias.Order.CreateTime) + "<=?";

            sql += " GROUP BY cast(substring(CONVERT(varchar,CreateTime,120), 12, 2) as int)";
            sql += " ORDER BY [Hour] ASC";

            Command.CommandText = sql;

            OleDbDataAdapter adapter = new OleDbDataAdapter();
            adapter.SelectCommand = Command;
            DataTable result = new DataTable();
            adapter.Fill(result);

            return Manager.BuildJson(result);
        }
        public string Agent_StatDaily(Guid agentId, Dictionary<string, object> data)
        {
            Command.Parameters.Clear();
            Command.Parameters.Add("@" + Alias.Order.DistributerIsAgent, MapType(Alias.Order.DistributerIsAgent)).Value = true;
            Command.Parameters.Add("@" + Alias.Order.DistributerID, MapType(Alias.Order.DistributerID)).Value = agentId;
            if (data.ContainsKey("IsFinished") && !String.IsNullOrEmpty(data["IsFinished"].ToString()))
                Command.Parameters.Add("@IsFinished", MapType(Alias.Order.IsFinished)).Value = data["IsFinished"];
            if (data.ContainsKey("IsArchived") && !String.IsNullOrEmpty(data["IsArchived"].ToString()))
                Command.Parameters.Add("@IsArchived", MapType(Alias.Order.IsArchived)).Value = data["IsArchived"];
            if (data.ContainsKey("IsCanceled") && !String.IsNullOrEmpty(data["IsCanceled"].ToString()))
                Command.Parameters.Add("@IsCanceled", MapType(Alias.Order.IsCanceled)).Value = data["IsCanceled"];
            if (data.ContainsKey("IsPayed") && !String.IsNullOrEmpty(data["IsPayed"].ToString()))
                Command.Parameters.Add("@IsPayed", MapType(Alias.Order.IsPayed)).Value = data["IsPayed"];
            if (data.ContainsKey("IsDelivered") && !String.IsNullOrEmpty(data["IsDelivered"].ToString()))
                Command.Parameters.Add("@IsDelivered", MapType(Alias.Order.IsDelivered)).Value = data["IsDelivered"];
            if (data.ContainsKey("Start") && !String.IsNullOrEmpty(data["Start"].ToString()))
                Command.Parameters.Add("@Start", MapType(Alias.Order.CreateTime)).Value = data["Start"];
            if (data.ContainsKey("End") && !String.IsNullOrEmpty(data["End"].ToString()))
                Command.Parameters.Add("@End", MapType(Alias.Order.CreateTime)).Value = data["End"];

            string sql = "SELECT CONVERT(varchar(10) , CreateTime, 23) as [Day], COUNT(*) as [Count], SUM(" + MapColumn(Alias.Order.Sum) + ") as [Sum] FROM " + MapTable(Alias.Objects.Order) + " WHERE " + MapColumn(Alias.Order.DistributerIsAgent) + "=? and " + MapColumn(Alias.Order.DistributerID) + "=?";
            if (data.ContainsKey("IsFinished") && !String.IsNullOrEmpty(data["IsFinished"].ToString()))
                sql += " and " + MapColumn(Alias.Order.IsFinished) + "=?";
            if (data.ContainsKey("IsArchived") && !String.IsNullOrEmpty(data["IsArchived"].ToString()))
                sql += " and " + MapColumn(Alias.Order.IsArchived) + "=?";
            if (data.ContainsKey("IsCanceled") && !String.IsNullOrEmpty(data["IsCanceled"].ToString()))
                sql += " and " + MapColumn(Alias.Order.IsCanceled) + "=?";
            if (data.ContainsKey("IsPayed") && !String.IsNullOrEmpty(data["IsPayed"].ToString()))
                sql += " and " + MapColumn(Alias.Order.IsPayed) + "=?";
            if (data.ContainsKey("IsDelivered") && !String.IsNullOrEmpty(data["IsDelivered"].ToString()))
                sql += " and " + MapColumn(Alias.Order.IsDelivered) + "=?";
            if (data.ContainsKey("Start") && !String.IsNullOrEmpty(data["Start"].ToString()))
                sql += " and " + MapColumn(Alias.Order.CreateTime) + ">=?";
            if (data.ContainsKey("End") && !String.IsNullOrEmpty(data["End"].ToString()))
                sql += " and " + MapColumn(Alias.Order.CreateTime) + "<=?";

            sql += " GROUP BY CONVERT(varchar(10) , CreateTime, 23)";
            sql += " ORDER BY [Day] ASC";

            Command.CommandText = sql;

            OleDbDataAdapter adapter = new OleDbDataAdapter();
            adapter.SelectCommand = Command;
            DataTable result = new DataTable();
            adapter.Fill(result);

            return Manager.BuildJson(result);
        }
        public string Agent_StatMonthly(Guid agentId, Dictionary<string, object> data)
        {
            Command.Parameters.Clear();
            Command.Parameters.Add("@" + Alias.Order.DistributerIsAgent, MapType(Alias.Order.DistributerIsAgent)).Value = true;
            Command.Parameters.Add("@" + Alias.Order.DistributerID, MapType(Alias.Order.DistributerID)).Value = agentId;
            if (data.ContainsKey("IsFinished") && !String.IsNullOrEmpty(data["IsFinished"].ToString()))
                Command.Parameters.Add("@IsFinished", MapType(Alias.Order.IsFinished)).Value = data["IsFinished"];
            if (data.ContainsKey("IsArchived") && !String.IsNullOrEmpty(data["IsArchived"].ToString()))
                Command.Parameters.Add("@IsArchived", MapType(Alias.Order.IsArchived)).Value = data["IsArchived"];
            if (data.ContainsKey("IsCanceled") && !String.IsNullOrEmpty(data["IsCanceled"].ToString()))
                Command.Parameters.Add("@IsCanceled", MapType(Alias.Order.IsCanceled)).Value = data["IsCanceled"];
            if (data.ContainsKey("IsPayed") && !String.IsNullOrEmpty(data["IsPayed"].ToString()))
                Command.Parameters.Add("@IsPayed", MapType(Alias.Order.IsPayed)).Value = data["IsPayed"];
            if (data.ContainsKey("IsDelivered") && !String.IsNullOrEmpty(data["IsDelivered"].ToString()))
                Command.Parameters.Add("@IsDelivered", MapType(Alias.Order.IsDelivered)).Value = data["IsDelivered"];
            if (data.ContainsKey("Start") && !String.IsNullOrEmpty(data["Start"].ToString()))
                Command.Parameters.Add("@Start", MapType(Alias.Order.CreateTime)).Value = data["Start"];
            if (data.ContainsKey("End") && !String.IsNullOrEmpty(data["End"].ToString()))
                Command.Parameters.Add("@End", MapType(Alias.Order.CreateTime)).Value = data["End"];

            string sql = "SELECT CONVERT(varchar(7) , CreateTime, 23) as [Month], COUNT(*) as [Count], SUM(" + MapColumn(Alias.Order.Sum) + ") as [Sum] FROM " + MapTable(Alias.Objects.Order) + " WHERE " + MapColumn(Alias.Order.DistributerIsAgent) + "=? and " + MapColumn(Alias.Order.DistributerID) + "=?";
            if (data.ContainsKey("IsFinished") && !String.IsNullOrEmpty(data["IsFinished"].ToString()))
                sql += " and " + MapColumn(Alias.Order.IsFinished) + "=?";
            if (data.ContainsKey("IsArchived") && !String.IsNullOrEmpty(data["IsArchived"].ToString()))
                sql += " and " + MapColumn(Alias.Order.IsArchived) + "=?";
            if (data.ContainsKey("IsCanceled") && !String.IsNullOrEmpty(data["IsCanceled"].ToString()))
                sql += " and " + MapColumn(Alias.Order.IsCanceled) + "=?";
            if (data.ContainsKey("IsPayed") && !String.IsNullOrEmpty(data["IsPayed"].ToString()))
                sql += " and " + MapColumn(Alias.Order.IsPayed) + "=?";
            if (data.ContainsKey("IsDelivered") && !String.IsNullOrEmpty(data["IsDelivered"].ToString()))
                sql += " and " + MapColumn(Alias.Order.IsDelivered) + "=?";
            if (data.ContainsKey("Start") && !String.IsNullOrEmpty(data["Start"].ToString()))
                sql += " and " + MapColumn(Alias.Order.CreateTime) + ">=?";
            if (data.ContainsKey("End") && !String.IsNullOrEmpty(data["End"].ToString()))
                sql += " and " + MapColumn(Alias.Order.CreateTime) + "<=?";

            sql += " GROUP BY CONVERT(varchar(7) , CreateTime, 23)";
            sql += " ORDER BY [Month] ASC";

            Command.CommandText = sql;

            OleDbDataAdapter adapter = new OleDbDataAdapter();
            adapter.SelectCommand = Command;
            DataTable result = new DataTable();
            adapter.Fill(result);

            return Manager.BuildJson(result);
        }
        #endregion
    }
}
